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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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: Puzzled by the run time of a query.
    ... Did not see any locks, ... The plan was presented and the runtime of the query is 3 ms. ... Hugo Kornelis, SQL Server MVP ... My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis ...
    (microsoft.public.sqlserver.server)
  • 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: Fetching Data from 2 tables and send in text File using query
    ... File so what can be the query to do so. ... How to embed this in DTS is beyond me, ... Hugo Kornelis, SQL Server MVP ...
    (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)