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
>Solution :
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.