sql – query for all values in table with limit

I have an SQL query which I run in Amazon Athena:

select 
    A,
    B,
    C,
    D,
from
    T
where
    A = '1000'
order by
    B desc
limit 1

where I order by B and take the first row only for the value 1000 for A. However I want to run this query for all values of A in T i.e for each A in T get the first row only and append to the results.

How do I do this?

Example of table data:

A     B           C D
1000 '12/01/2021' 1 7
1000 '10/01/2020' 2 8
1333 '06/01/1920' 3 9
1333 '07/01/1920' 4 10
1999 '09/03/1960' 5 11
1999 '09/03/1950' 6 12

and the result I want to get is:

1000 '12/01/2021' 1 7
1333 '07/01/1920' 4 10
1999 '09/03/1960' 5 11

Answer

You can try to use ROW_NUMBER window function to make it.

SELECT A,
    B,
    C,
    D
FROM (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC) rn
    FROM T
) t1
WHERE rn = 1

Advertisement

Answer