Thursday, 22 November 2012

T-SQL: Selecting the top n rows from a Group By


I have the following data


deal_tracking_num
tran_num
type_id
value
personnel_id
last_update
897693
897693
20065
Manual
20015
2012-11-22 10:59:38.240
897693
897693
20065
Non-STP
20015
2012-11-22 11:09:09.190
897695
897695
20065
Manual
20015
2012-11-22 10:58:58.063
897695
897702
20065
Manual
20015
2012-11-22 10:59:23.493
897698
897698
20065
STP
20015
2012-11-22 10:16:23.903
897698
897698
20065
eCM
20015
2012-11-22 10:18:38.587
897698
897701
20065
eCM
20015
2012-11-22 10:21:07.350
897698
897703
20065
eCM
20015
2012-11-22 11:22:25.377

To select the first row for each deal_tracking_num, use the following query:

select a.deal_tracking_num, a.value from 
(
     SELECT ab.deal_tracking_num, ati.*, ROW_NUMBER() over (partition by deal_tracking_num ORDER BY ab.deal_tracking_num, ati.last_update) as rank
     FROM ab_tran_info_history ati JOIN ab_tran ab ON ab.tran_num = ati.tran_num
     WHERE ati.type_id = 20065 -- Trade Confirmation Type
) a
where RANK = 1

This returns

897693
Manual
897695
Manual
897698
STP

No comments:

Post a Comment