SQL TOP 1 self join query - SQL Server Forums

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)

SQL TOP 1 self join query

Author

Topic

stamford
Starting Member

47 Posts

Posted - 2014-01-28 : 11:50:00

If duplicate dates are ignored and multiples are returned, what script would return a single column of TREATMENT_ID values for each CARE_ID where the value of TREATMENT_DATE is the oldest?
The script has to be backwards compatible with SQL2000. Thank you.

CARE_ID	TREATMENT_ID	TREATMENT_DATE
369	117	        28/07/2010 00:00
481	63	        07/09/2010 00:00
834	622	        01/01/2012 00:00
2222	434	        11/05/2011 00:00
2246	138	        01/07/2012 00:00
2246	413	        01/07/2012 00:00
2246	768	        01/07/2012 00:00
2265	448	        31/05/2011 00:00
2512	628	        26/01/2012 00:00
3311	796	        11/09/2012 00:00
3311	434	        08/01/2013 00:00
3362	808	        08/02/2013 00:00
3362	440	        10/02/2013 00:00
3362	448	        05/04/2013 00:00
3362	819	        05/04/2013 00:00

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-01-28 : 16:02:03

what is the expected output based on this sample data?

Be One with the Optimizer
TG

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-01-28 : 16:41:55

I see a problem with your question and your sample data.. Look at 2246 Care_id, it has different treatment_id's but same treatment_date. In that scenario, do you want me to consider 768 as the treatment_id because it has the highest number?

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-01-28 : 16:55:42

Use below...


CREATE TABLE [dbo].[rank] (
    [pk_id] [int] IDENTITY (1, 1) NOT NULL ,
    [care_id] INT,
    [tid] INT,
    [tdate] [datetime] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO dbo.[rank](CARE_ID,	tid, tdate) VALUES(369,	117, '2010/07/28')
,(481,63,'2010/07/09'), (834,622,'2012/01/01'), (2222, 434, '2011/05/11')
,(2246,138, '2012/07/01'), (2246, 413, '2012/07/01')
,(2246,768, '2012/07/01')

select [R1].[pk_id],[R1].[care_id], [R1].tid, [R1].[tdate], rank =
(select count(distinct [R2].[tid])
from [rank] [R2]
where [R1].[care_id] = R2.care_id AND r1.tid <= [R2].[tid]
)
from [rank] [R1]
WHERE (select count(distinct [R2].[tid])
from [rank] [R2]
where [R1].[care_id] = R2.care_id AND r1.tid <= [R2].[tid]
) = 1
order by [rank]

DROP TABLE [rank]

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources