I have a table of transactions with columns: ID and DATE (integer) [ddmmyyyy]
One ID can have more than one transaction. This means ID are repeated in the table.
I need to write a query that returns for each ID the transaction with the highest date.
Executing : Select * from transacciones
Output actual
| ID | DATE |
|---|---|
| 1 | 01012022 |
| 2 | 02012022 |
| 3 | 02012022 |
| 1 | 03012022 |
| 3 | 03012022 |
Output deseado:
| ID | DATE |
|---|---|
| 1 | 03012022 |
| 2 | 02012022 |
| 3 | 03012022 |
CodePudding user response：
SELECT ID, MAX(DATE) FROM transacciones GROUP BY ID;
Would work in theory, however as nacho commented, you're going to inevitable have issues with your data trying to store dates as integers (or even strings). They should be stored as DATE or TIMESTAMP.
CodePudding user response：
SELECT ID, MAX(DATE) FROM transacciones GROUP BY ID;