I am looking for a query which will help me to find the Account_no, latest order and the effective date of the latest order on that account for all the accounts in the table. Sample table as below.
| Account_no | order_id | effective_date |
|---|---|---|
| 100 | ord1 | 01012023 |
| 100 | ord2 | 03042023 |
| 101 | ord1 | 01012023 |
Query result:
| Account_no | order_id | effective_date |
|---|---|---|
| 100 | ord2 | 03042023 |
| 101 | ord1 | 01012023 |
SELECT ACCOUNT_NO,TOP1 ORDID FROM ORDER.