?
Solved

SQLquery using  Group BY and Top 1

Posted on 2011-10-26
9
Medium Priority
?
210 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:byte1
9 Comments
 
LVL 6

Expert Comment

by:jorgedeoliveiraborges
ID: 37034359
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
 

Author Comment

by:byte1
ID: 37034362
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
 

Author Comment

by:byte1
ID: 37034368
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 6

Expert Comment

by:jorgedeoliveiraborges
ID: 37034382


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
 
LVL 6

Expert Comment

by:jorgedeoliveiraborges
ID: 37034389
excuse-me.
0
 

Author Comment

by:byte1
ID: 37034501
@jorgedeoliveiraborges: Sorry, I do not understand your solution. It is not returning back the result like i needed.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 37034603
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 37035075
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
 

Author Closing Comment

by:byte1
ID: 37036098
Awesome solution. Thanks Scott.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Screencast - Getting to Know the Pipeline

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question