Menu
I have table like this:
a b 1 23 1 2 1 7 2 9 2 11 I want to select the first row(order does not matter) from a "GROUP BY a" query , the result should be
a b 1 23 2 9 I am using SQL SERVER 2008 how to write the query for this?
785
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.
You can use row_number() to get the row number of the row. It uses the over command - the partition by clause specifies when to restart the numbering and the order by selects what to order the row number on.
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.
While the table name is selected type CTRL + 3 and you will notice that the query will run and will return a single row as a resultset. Now developer just has to select the table name and click on CTRL + 3 or your preferred shortcut key and you will be able to see a single row from your table.
select a,b from ( select a,b,row_number() over(partition by a order by b desc) as roworder from myTable ) temp where roworder = 1 see http://msdn.microsoft.com/en-us/library/ms186734.aspx
130
If as you indicated, order doesn't matter, any aggregate function on b would be sufficient.
SELECT a, b = MIN(b) FROM YourTable GROUP BY a If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
