Hi all,
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
varchar(64).
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
so on)
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
different devices.
Is it possible?

Thanks for your help"

and Mohit K. Gupta helped with this code, that works great:

"SELECT Device_Name,
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?
Thanks

On Fri, 22 May 2009 08:02:03 -0700, Big Passeron wrote:

Hi Big Passeron,

Here's one possible way:

SELECT d.Device_Name,
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