Help for a simple (?) query

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

Tr7 ..

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

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/


"Big Passeron" wrote:

> 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