CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
jere
jere

Reputation: 4304

SELECT 5 most recent SQL Server

I have a table with records like this:

id        timestamp               dose             drug_id
1     2012-10-04 09:10:54          05                 2
1     2012-10-04 09:12:34          15                 2
1     2012-10-04 09:15:12          20                 2
1     2012-10-04 09:35:32          25                 2
1     2012-10-04 09:37:34          25                 2
1     2012-10-04 09:39:24          25                 2
1     2012-10-04 09:42:16          35                 2
1     2012-10-04 09:43:07          35                 2

What I want to do is select the last 5 used dose values for a given drug, so in this case the query should return 35, 25, 20, 15, 05.

I know I can use TOP and ORDER BY to get the latest 5 entries, but in this case it would return duplicates (35, 35, 25, 25, 25).

What should I use to get the output I want?

Upvotes: 6

Views: 10033

Answers (3)

Niladri Biswas
Niladri Biswas

Reputation: 4171

Will this help 

DECLARE @T TABLE(ID INT,Time_Stamp DATETIME,Dose INT, Drug_Id INT)
INSERT INTO @T VALUES
(1,'2012-10-04 09:10:54',05,2),
(1,'2012-10-04 09:12:34', 15,2),
(1,'2012-10-04 09:15:12',20,2),
(1,'2012-10-04 09:35:32',25,2),
(1,'2012-10-04 09:37:34',25,2),
(1,'2012-10-04 09:39:24',25,2),
(1,'2012-10-04 09:42:16',35,2),
(1,'2012-10-04 09:43:07',35,2)


;WITH CTE AS(
SELECT *, Rn = ROW_NUMBER()OVER(PARTITION BY Dose ORDER BY Time_Stamp DESC) 
FROM @T)
SELECT TOP 5 ID,Time_Stamp,Dose,Drug_Id 
FROM CTE
WHERE Rn = 1
ORDER BY Dose DESC

enter image description here

Upvotes: 3

Anil Soman
Anil Soman

Reputation: 2467

SELECT top 5 dose
FROM table_id
GROUP BY dose
ORDER BY max(timestamp) desc

Upvotes: 3

Hotchips
Hotchips

Reputation: 633

I don't have access to my server to test this, but should not

SELECT TOP 5 dose 
FROM table_id 
GROUP BY dose 
ORDER BY max(time) desc;

work?

EDIT: Tested on http://sqlfiddle.com/#!6/610c4/2, note that I haven't used timestamp merely time. Fix appropriately.

Upvotes: 12

Related Questions