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