SQLTeam.com | Weblogs | Forums

How to apply filters within record groups?

Hello

In the table below, how can the records be filtered so that only the max Col2 is taken per group of Col1 values?

Col1 Col2 Col3
A 01/01/2000 1
A 02/01/2000 2
A 03/01/2000 3
B 04/01/2000 4
B 05/01/2000 5
B 06/01/2000 6

Expected result:

Col1 Col2 Col3
A 03/01/2000 3
B 06/01/2000 6

I tried the below without luck:
select * from Table1 having Col2=max(Col2) group by Col1

I remember there is a way to group records then sort then take the first but I wonder if there is a simpler way?

Thanks!

you have been posting on this site for a good while to know you need to provide useable data.
please do

declare @sample(Col1 char(1), col2 datetime, col3 int)

insert into @sample

Help us help you.

2 Likes

SELECT Col1, Col2, Col3
FROM (
    SELECT *,
        ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2 DESC) AS row_num
    FROM dbo.table_name
) AS derived
WHERE row_num = 1
ORDER BY Col1
1 Like

hi

a different way of doing this

if we talk about performance , execution plan and all that ... that is something else

select top 1 with ties col1,col2,col3 from Table1 order by row_number() over (partition by col1 order by col2 desc)

image