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

## 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

**5**People found this is helpful