CODEWITHSUNDEEP
sqlsql-servert-sql
Jez
Jez

Reputation: 30003

Is it possible to filter grouped SQL rows?

I want to group rows together but then filter each group. So if I have the following table:

mytable

id | filename | region | text
------------------------------------
1  | aaa      | 1      | Region1 aaa
2  | bbb      | 2      | Region2 bbb
3  | aaa      | 5      | Region5 aaa
4  | bbb      | 5      | Region5 bbb

I want to group by filename and get out the row in each group with the MAX region number. This gets the data into the appropriate groups:

SELECT filename
FROM mytable
GROUP BY filename

... but can I then pick out the entry in each group with the highest region value, which in this case would be the rows with IDs 3 and 4? The HAVING clause only filters entire groups, not rows within groups.

Upvotes: 0

Views: 107

Answers (2)

paparazzo
paparazzo

Reputation: 45096

select  *
from ( select * 
            , row_number() over(partition by filename order by region desc) as rn
       from table 
     ) tt 
where rn = 1

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use row_number.

select top 1 with ties *
from tbl
order by row_number() over(partition by filename order by region desc)

Upvotes: 1

Related Questions