Finding Top Row in a Group of Columns (Partition)



Declare @table table (cnsmr_id bigint,	cnsmr_phn_typ varchar(1),	cnsmr_phn_nmbr varchar(20),	Updatedttm datetime)

Insert into @table values (1,1,'123456789','2014-06-28 08:23:22.460')
,(1,2,'123456289','2014-09-26 08:23:22.460')
,( 1,3,'123456189','2014-09-26 08:23:22.460')
,(1,1,'1234561189','2014-09-26 08:23:22.460')
,(1,2,'12345672289','2014-10-06 08:23:22.460')
,(2,1,'1234567389','2014-09-26 08:23:22.460')
,( 2,2,'12345678119','2014-09-26 08:23:22.460')
,( 2,3,'12345678229','2014-09-26 08:23:22.460')

--SQL Server 2012 and 2014
--the ROWS clause is specified with UNBOUNDED PRECEDING. The result is that the window starts at the first row of the partition.

Select cnsmr_id,cnsmr_phn_typ, Updatedttm from (
select *,max(Updatedttm) OVER (PARTITION BY cnsmr_id, cnsmr_phn_typ 
ORDER BY Updatedttm desc  ROWS UNBOUNDED PRECEDING) maxdt
from @table ) t
WHERE Updatedttm =maxdt
  order by cnsmr_id,cnsmr_phn_typ, Updatedttm desc


-- typically we use a row_number function
;with mycte as ( 
select *,row_number() over (partition by cnsmr_id , cnsmr_phn_typ order by Updatedttm desc) cn
 from @table
 ) 
 Select cnsmr_id,cnsmr_phn_typ, Updatedttm from mycte 
 WHERE cn=1 
 order by cnsmr_id,cnsmr_phn_typ, Updatedttm desc



 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s