SQLquery using Group BY and Top 1

I need to find the Top 1 result from a Group By clause.

Table 1 : a
id     dev id    Timestamp
1          2        01/01/2011 01:00:00
2          2        01/01/2011 02:00:00
3          2        01/01/2011 03:00:00
1          3        01/01/2011 01:00:00

Result needed:
id         dev id     timestamp
3          2        01/01/2011 03:00:00
1          3        01/01/2011 01:00:00
byte1Asked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
I'm guessing you're after the latest date for each dev id:
SELECT id, [dev id], [timestamp]
FROM (
SELECT id, [dev id], [timestamp], ROW_NUMBER() OVER(PARTITION BY [dev id] ORDER BY [timestamp] DESC) AS row_num
FROM [Table 1]
) AS derived
WHERE row_num = 1
ORDER BY [dev id]

Open in new window

0
 
jorgedeoliveiraborgesCommented:
use adventureworks;
go

    SELECT top 1
           ProductSubcategoryID, 
           sum (ListPrice)
    FROM Production.Product as p
    group by ProductSubcategoryID
    order by ProductSubcategoryID asc;

Open in new window

0
 
byte1Author Commented:
It should return only the top 1 of each group, the group is ordered by the timestamp. I need only those dev id that have the latest date.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
byte1Author Commented:
I realized i made a mistake while posting the question.

Table 1 : a
id     dev id    Timestamp
1          2        01/01/2011 01:00:00
2          2        01/01/2011 02:00:00
3          2        01/01/2011 03:00:00
4          3        01/01/2011 01:00:00

Result needed:
id         dev id     timestamp
3          2        01/01/2011 03:00:00
4          3        01/01/2011 01:00:00
0
 
jorgedeoliveiraborgesCommented:


use adventureworks;
go

    SELECT top 1
           ProductSubcategoryID, 
           sum (ListPrice)
    FROM Production.Product as p
    group by ProductSubcategoryID
    order by ProductSubcategoryID asc;

Open in new window


ProductSubcategoryID
-------------------- ---------------------
NULL                 1432,20

(1 row(s) affected)
0
 
jorgedeoliveiraborgesCommented:
excuse-me.
0
 
byte1Author Commented:
@jorgedeoliveiraborges: Sorry, I do not understand your solution. It is not returning back the result like i needed.
0
 
Kevin CrossChief Technology OfficerCommented:
Please credit Scott for the answer. That is the route I would go with SQL 2008. If the topic area is incorrect or you just want to learn more, please read http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_1555-Analytical-SQL-Where-do-you-rank.html which shows how to do ranking in SQL 2000 also.

Again, your answer is above, but in your case without ROW_NUMBER() you can do this with an INNER JOIN to a derived query that gets the MAX([timestamp]) GROUP BY [dev id].
0
 
byte1Author Commented:
Awesome solution. Thanks Scott.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.