How to return rows based on three 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)

How to return rows based on three columns

Author

Topic

stamford
Starting Member

47 Posts

Posted - 2014-03-21 : 13:46:35

In the following example table what script would return a single row for each PERSON_ID which has a MAX(PRE_EVENT_DATE) which is <= ACTUAL_EVENT_DATE and if there are two values of PRE_EVENT_DATE the same then the highest value of EVENT_ID is the decider.



PERSON_ID	EVENT_ID	PRE_EVENT_DATE	        EVENT_TYPE	ACTUAL_EVENT_DATE
369	        902	        27/07/2010 00:00	TE	        28/07/2010 00:00
369	        117	        28/07/2010 00:00	CH	        28/07/2010 00:00
481	        200	        29/07/2010 00:00	CH	        07/09/2010 00:00
481	        12	        30/07/2010 00:00	CH	        07/09/2010 00:00
481	        63	        31/07/2010 00:00	TE	        07/09/2010 00:00
3213	        377	        20/01/2012 00:00	TE	        20/01/2012 00:00
3515	        789	        15/06/2012 00:00	TE	        16/09/2013 00:00
3773	        1196	        30/01/2013 00:00	CH	        10/02/2013 00:00
4730	        1262	        22/11/2012 00:00	CH	        30/11/2012 00:00
4863	        1322	        22/11/2012 00:00	CH	        11/12/2012 00:00
4930	        1330	        21/11/2012 00:00	CH	        11/12/2012 00:00
4961	        1335	        22/11/2012 00:00	CH	        11/12/2012 00:00
4961	        1333	        22/11/2012 00:00	CH	        11/12/2012 00:00
4961	        1201	        22/11/2012 00:00	CH	        11/12/2012 00:00
5798	        1549	        02/11/2013 00:00	CH	        04/11/2013 00:00
5870	        1561	        11/12/2013 00:00	CH	        11/12/2013 00:00
6460	        1699	        05/01/2014 00:00	CH	        10/01/2014 00:00

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-21 : 14:09:06

Something along these lines, which uses row_number function

SELECT * FROM
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY PERSON_ID ORDER BY PRE_EVENT_DATE DESC, EVENT_ID DESC) AS RN
FROM YourTable
WHERE PRE_EVENT_DATE <= ACTUAL_EVENT_DATE 
)s WHERE RN=1;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources