Select 3 max values from a table

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 2000 Forums

Transact-SQL (2000)

Select 3 max values from a table

Author

Topic

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-09-09 : 21:58:40

Hi All,

I need to select last 3 appointments for each id in descending order

Here is the sample data:
CREATE TABLE #Sample (CaseID int, ApptDate DATETIME)

INSERT INTO #Sample
SELECT 22, '1/5/2007' UNION ALL
SELECT 22, '1/10/2007' UNION ALL
SELECT 22, '1/8/2007' UNION ALL
SELECT 22, '3/25/2007' UNION ALL
SELECT 22, '3/22/2007' UNION ALL
SELECT 22, '3/24/2007' UNION ALL
SELECT 33, '2/2/2007' UNION ALL
SELECT 33, '1/17/2007' UNION ALL
SELECT 33, '2/23/2007' UNION ALL
SELECT 33, '3/5/2007' UNION ALL
SELECT 44, '3/5/2007' UNION ALL
SELECT 44, '1/16/2007' UNION ALL
SELECT 55, '2/15/2007' UNION ALL
SELECT 55, '1/16/2007' UNION ALL
SELECT 55, '1/20/2007' UNION ALL
SELECT 55, '3/22/2007' UNION ALL
SELECT 55, '2/20/2007'

and update another table prepopulated with ids
CREATE TABLE #Result (id int, Appt3 datetime, Appt2 datetime, Appt1 datetime)
SELECT 22, null, null, null UNION ALL
SELECT 33, null, null, null UNION ALL
SELECT 44, null, null, null UNION ALL
SELECT 55, null, null, null

Expected result: table #Result should have data like this
22, '3/25/2007', '3/24/2007', '3/22/2007'
33, '3/5/2007', '2/23/2007', '2/2/2007'
44, '3/5/2007', '1/16/2007', null
55, '3/22/2007', '2/20/2007', '2/15/2007'

Any help is greatly appreciated.

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-09-10 : 06:06:32

SELECT
A.CaseID,
MAX(A.ApptDate),
(SELECT MAX(Z.ApptDate) FROM #Sample Z WHERE Z.CaseID = A.CaseID AND Z.ApptDate < MAX(A.ApptDate)),
(SELECT MAX(Z.ApptDate)
FROM #Sample Z
WHERE Z.CaseID = A.CaseID AND Z.ApptDate < ((SELECT MAX(Y.ApptDate) FROM #Sample Y WHERE Y.CaseID = A.CaseID AND Y.ApptDate < MAX(A.ApptDate))))
FROM #Sample A
GROUP BY A.CaseID
ORDER BY A.CaseID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 06:10:05

[code]DECLARE @Sample TABLE (CaseID INT, ApptDate DATETIME)

INSERT @Sample
SELECT 22, '1/5/2007' UNION ALL
SELECT 22, '1/10/2007' UNION ALL
SELECT 22, '1/8/2007' UNION ALL
SELECT 22, '3/25/2007' UNION ALL
SELECT 22, '3/22/2007' UNION ALL
SELECT 22, '3/24/2007' UNION ALL
SELECT 33, '2/2/2007' UNION ALL
SELECT 33, '1/17/2007' UNION ALL
SELECT 33, '2/23/2007' UNION ALL
SELECT 33, '3/5/2007' UNION ALL
SELECT 44, '3/5/2007' UNION ALL
SELECT 44, '1/16/2007' UNION ALL
SELECT 55, '2/15/2007' UNION ALL
SELECT 55, '1/16/2007' UNION ALL
SELECT 55, '1/20/2007' UNION ALL
SELECT 55, '3/22/2007' UNION ALL
SELECT 55, '2/20/2007'

SELECT CaseID,
MAX(CASE WHEN RecID = 0 THEN ApptDate ELSE NULL END) AS Appt1,
MAX(CASE WHEN RecID = 1 THEN ApptDate ELSE NULL END) AS Appt2,
MAX(CASE WHEN RecID = 2 THEN ApptDate ELSE NULL END) AS Appt3
FROM (
SELECT s.CaseID,
s.ApptDate,
(SELECT COUNT(*) FROM @Sample AS k WHERE k.CaseID = s.CaseID AND k.ApptDate > s.ApptDate) AS RecID
FROM @Sample AS s
) AS g
GROUP BY CaseID
ORDER BY CaseID[/code]

E 12°55'05.25"
N 56°04'39.16"

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-09-10 : 09:00:11

Thanks a lot guys.
You're the best.

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-09-10 : 15:03:08

Just one more thing.

The script you guys wrote is working except of 1 situation,
if (the 1st and 2nd) or (2nd and 3rd) or (all three) ApptDates are the same, i.e. all 3 dates are like '03/05/2007'. Unfortunately I don't have time part of the datetime field since I'm getting these dates from the varchar field in the table. Please don't blame me for this, I inherited this DB design and I can't change it.
What would you suggest?

TIA.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 15:16:10

You are still using SQL Server 2000?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 16:07:36

[code]-- Prepare sample data to mimic your environment
DECLARE @Sample TABLE (CaseID INT, ApptDate DATETIME)

INSERT @Sample
SELECT 22, '1/5/2007' UNION ALL
SELECT 22, '1/10/2007' UNION ALL
SELECT 22, '1/8/2007' UNION ALL
SELECT 22, '3/25/2007' UNION ALL
SELECT 22, '3/25/2007' UNION ALL
SELECT 22, '3/22/2007' UNION ALL
SELECT 22, '3/24/2007' UNION ALL
SELECT 33, '2/2/2007' UNION ALL
SELECT 33, '1/17/2007' UNION ALL
SELECT 33, '2/23/2007' UNION ALL
SELECT 33, '2/23/2007' UNION ALL
SELECT 33, '3/5/2007' UNION ALL
SELECT 44, '3/5/2007' UNION ALL
SELECT 44, '1/16/2007' UNION ALL
SELECT 55, '2/15/2007' UNION ALL
SELECT 55, '1/16/2007' UNION ALL
SELECT 55, '1/20/2007' UNION ALL
SELECT 55, '3/22/2007' UNION ALL
SELECT 55, '2/20/2007'

-- This code below is the code you need

-- Stage the data
DECLARE @Stage TABLE (RowID INT IDENTITY, CaseID INT, ApptDate DATETIME)

INSERT @Stage
SELECT CaseID,
ApptDate
FROM @Sample
ORDER BY CaseID,
ApptDate DESC

-- Show the expected output
SELECT CaseID,
MAX(CASE WHEN RecID = 0 THEN ApptDate ELSE NULL END) AS Appt1,
MAX(CASE WHEN RecID = 1 THEN ApptDate ELSE NULL END) AS Appt2,
MAX(CASE WHEN RecID = 2 THEN ApptDate ELSE NULL END) AS Appt3
FROM (
SELECT s.RowID - (SELECT MIN(w.RowID) FROM @Stage AS w WHERE w.CaseID = s.CaseID) AS RecID,
s.CaseID,
s.ApptDate
FROM @Stage AS s
) AS g
GROUP BY CaseID
ORDER BY CaseID[/code]

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-10 : 16:14:25

"You are still using SQL Server 2000?"

I know its not a reliable indicator of question-quality

but this is a SQL-2000 forum!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 16:17:34

Yes, but he might be in process of migrating and "forgot" to tell us.
And maybe he isn't even aware of SQL Server 2005 being better in these things than SQL Server 2000?

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-10 : 16:23:11

OMG, its going to one of those impenetrable CROSS APPLY solutions in SQL 2005 isn't it?

I can't wait to see it, but I know I won't understand ti

Kristen

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-09-10 : 16:31:38

Yes, for this application we still using SQL 2000.
I don't like it either, but it's not my call. The company plans to move all apps to SQL 2005, but... planning, planning.
Anyway, Peso, thanks for the solution

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources