Help for a simple (?) query - part 2 - 05-22-2009 , 10:02 AM
sometimes ago I posted this question, but I cannot find the thread anymore:
"I'm stuck with a (I think) simple query.
A table (Extracted_Data) is made of a PK (id), a date value (Data_Time) and
a int (iddevice).
Another table (Device) holds a relationship with the previous table, by
means of its PK column id, while the other column (Device_Name) is
I'd like to be able to find just the last data extracted by every device
(e.g. Device1's last value was taken on 07/12/08, Device2's on 11/10/08 and
This way, assuming that Extracted Data contains 30000 rows and Devices
contains 50 rows, I want the query to retrieve 50 distinct rows from 50
Is it possible?
Thanks for your help"
and Mohit K. Gupta helped with this code, that works great:
Last_Used = ( SELECT TOP 1 LastUsedDate
FROM Extracted_Data AS ED
WHERE ED.IDDevice = D.ID
ORDER BY LastUsedDate DESC)
FROM Devices AS D"
Now I need to retrieve the last 10 data for each device. How can I modify
the above query to make it work?
Re: Help for a simple (?) query - part 2 - 05-22-2009 , 03:10 PM
On Fri, 22 May 2009 08:02:03 -0700, Big Passeron wrote:
Here's one possible way:
e.Data_Time AS Last_Used
FROM Devices AS d
INNER JOIN Extracted_Date AS e
ON e.IDDevice = d.ID
WHERE (SELECT COUNT(*)
FROM Extracted_Date AS newer
WHERE newer.IDDevice = e.IDDevice
AND newer.Data_Time > e.Data_Time) < 10;
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply).
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis