sql server select first row from a group

Sql Server

Sql Server Problem Overview


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?

Sql Server Solutions


Solution 1 - Sql Server

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

Solution 2 - Sql Server

If as you indicated, order doesn't matter, any aggregate function on b would be sufficient.

Example Using MIN

SELECT a, b = MIN(b)
FROM   YourTable
GROUP BY
       a

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionRn2dyView Question on Stackoverflow
Solution 1 - Sql Serverremi bourgarelView Answer on Stackoverflow
Solution 2 - Sql ServerLieven KeersmaekersView Answer on Stackoverflow