I have an SQL query which I run in Amazon Athena:
x
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
Advertisement
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