Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Retrieving rows based on multiple columns

Author  Topic 

stamford
Starting Member

47 Posts

Posted - 2014-10-01 : 11:48:51
In the sample table below I need a script to return the last instance of event_type CP before the first instance of S. The primary comparison priority is the most recent event_date, and the secondary priority is event_id.
In the event of duplicate dates the tie breaker will be the highest event_id.
So the resulting table will be

person_id event_id event_date event_type
3907 2896 09/09/2013 00:00 CP
4040 3659 15/05/2014 00:00 CP




person_id event_id event_date event_type
3907 2457 01/09/2013 00:00 CP
3907 2896 09/09/2013 00:00 CP
3907 1866 20/05/2014 00:00 S
4040 3660 10/05/2014 00:00 CP
4040 3659 15/05/2014 00:00 CP
4040 1866 20/05/2014 00:00 S

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-10-06 : 01:23:22
like following ?
SELECT
person_id, event_id, event_date, event_type
FROM (
SELECT
ROW_NUMBER() OVER(PARTITION BY person_id ORDER BY event_date DESC, event_id DESC) row_num,
*
FROM SourceTable
WHERE event_type = 'CP'
) TBL
WHERE row_num = 1


-------------------------------------
From Japan
Sorry, my English ability is limited.
Go to Top of Page
   

- Advertisement -