Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I select the first row per group in an SQL Query?

I've got this SQL query:

SELECT   Foo, Bar, SUM(Values) AS Sum
FROM     SomeTable
GROUP BY Foo, Bar
ORDER BY Foo DESC, Sum DESC

This results in an output similar to this:

47  1   100
47  0   10
47  2   10
46  0   100
46  1   10
46  2   10
44  0   2

I'd like to have only the first row per Foo (it and its highest Bar), and ignore the rest.

47  1   100
46  0   100
44  0   2

How do I do that?

like image 616
mafu Avatar asked Dec 10 '09 15:12

mafu


People also ask

How do I get the first row in a query?

To return only the first row that matches your SELECT query, you need to add the LIMIT clause to your SELECT statement. The LIMIT clause is used to control the number of rows returned by your query. When you add LIMIT 1 to the SELECT statement, then only one row will be returned.

How do I select specific rows in SQL?

To select rows using selection symbols for character or graphic data, use the LIKE keyword in a WHERE clause, and the underscore and percent sign as selection symbols. You can create multiple row conditions, and use the AND, OR, or IN keywords to connect the conditions.

Can we use select * with group by?

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause. The original idea was to create the table in beginning of the query, so the (SELECT * FROM #TBL) could be used on the query itself, instead of defining the names on each GROUP BY.

Can we use top with Group By clause?

Typically, these are accomplished using the TOP or LIMIT clause. Problem is, Top N result sets are limited to the highest values in the table, without any grouping. The GROUP BY clause can help with that, but it is limited to the single top result for each group.


1 Answers

declare @sometable table ( foo int, bar int, value int )

insert into @sometable values (47, 1, 100)
insert into @sometable values (47, 0, 10)
insert into @sometable values (47, 2, 10)
insert into @sometable values (46, 0, 100)
insert into @sometable values (46, 1, 10)
insert into @sometable values (46, 2, 10)
insert into @sometable values (44, 0, 2)

WITH cte AS 
(
    SELECT   Foo, Bar, SUM(value) AS SumValue, ROW_NUMBER() OVER(PARTITION BY Foo ORDER BY FOO DESC, SUM(value) DESC) AS RowNumber
    FROM     @SomeTable
    GROUP BY Foo, Bar
)
SELECT * 
FROM cte
WHERE RowNumber = 1
like image 146
Anton Georgiev Avatar answered Sep 27 '22 17:09

Anton Georgiev