SELECT 5 latest SQL Server

Question

I have a table with the following entries:

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 this drug, so in this case, the query should return 35, 25, 20, 15, 05 .

I know that I can use TOP and ORDER BY to get the last 5 records, but in this case it will return duplicates (35, 35, 25, 25, 25).

What should I use to get the desired result?

Answer 1

I do not have access to my server to check this, but should not

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

Work?

EDIT: Tested at http://sqlfiddle.com/#!6/610c4/2 , note that I did not use timestamp just time . Fasten accordingly.

Answer 2

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

Answer 3

Will it 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