Re: Help for a simple (?) query - part 2

Works great!
Thanks a lot!

"Hugo Kornelis" wrote:

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

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

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

.

Relevant Pages

  • Re: SQL query question #2
    ... and having test data makes it easier to test. ... Hugo Kornelis, SQL Server MVP ... My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis ... E.g. if I query the above data with required values of 'b' and 'd'. ...
    (comp.databases.ms-sqlserver)
  • Re: How To Return A "Range Of Rows"??
    ... Shouldn't I be seeing results with the column headers? ... this just to show that the query works in debug mode? ... Hugo Kornelis, SQL Server MVP ... My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis ...
    (comp.databases.ms-sqlserver)
  • Re: Importing from another database on same server using SQL Server Express
    ... I thought this post was considered dead, so I elected to repost the ... "amish" on that group proved to work: ... Please post the COMPLETE query, ... Hugo Kornelis, SQL Server MVP ...
    (microsoft.public.sqlserver.msde)
  • Re: reduce time for search query
    ... execute. ... The query fetches records which will have to sorted by ... Hugo Kornelis, SQL Server MVP ... My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis ...
    (comp.databases.ms-sqlserver)
  • Re: How To Return A "Range Of Rows"??
    ... SET ROWCOUNT. ... there are no rows returned by your query. ... the proc later tries to set a negative ROWCOUNT. ... Hugo Kornelis, SQL Server MVP ...
    (comp.databases.ms-sqlserver)
Loading