Retrieving rows based on multiple columns

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources