SQLTeam.com | Weblogs | Forums

How to apply filters within record groups?

#1

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!

#2

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

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

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