Given the following table:
| ID | Price | Date |
|---|---|---|
| 1 | 34 | a |
| 1 | 42 | b |
| 2 | 34 | a |
I would like to have one row per ID where the price was maximal
| ID | Price | Date |
|---|---|---|
| 1 | 42 | b |
| 2 | 34 | a |
Trying to groupby ID and selecting ID, Date, MAX(Price) results in the error GROUP BY clause with non aggregate functions
CodePudding user response：
You can use row_number.
SELECT *
FROM
your_table_name
QUALIFY ROW_NUMBER() OVER (partition by id order by price desc) = 1;
The Qualify clause is used to filter the results of ordered analytical function according to user‑specified search conditions. We can use this conditional clause in the SELECT statement to get the particular order values.