sql server select first row from a group

Question

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?

So `(1,2),(2,11)` would also be acceptable as order doesn't matter? (Or any other arbitrary `b` value per `a`) — Martin Smith, Sep 08 '11 at 07:45
Does this answer your question? [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Michael Freidgeim, Apr 22 '21 at 08:31
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Michael Freidgeim, May 17 '22 at 13:19

score 150 · Answer 1 · answered Sep 08 '11 at 07:46

150

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

answered Sep 08 '11 at 07:46

remi bourgarel

7

When there's only one other value to pull from the row from each group (as here), it's simpler to just use `MIN` or `MAX` (your example is equivalent to just selecting `MAX(b)`). Of course, for more columns, it makes more sense. – Damien_The_Unbeliever Sep 08 '11 at 07:51
Though the query looks complex but gives the flexibility to select multiple columns. – Ankur Arora Dec 21 '21 at 11:28
Even if your column is not numeric MAX/MIN will work ;-) – Mauricio Gracia Gutierrez Dec 29 '21 at 16:41

Lieven Keersmaekers · Accepted Answer · 2013-10-16T16:43:34.853

81

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

edited Oct 16 '13 at 16:43

answered Sep 08 '11 at 07:48

1

Thank you Lieven, but this selects only one particular value. What if there were more columns and we need to select whole ROW (as the title says)? – dpelisek Feb 13 '14 at 16:51
1

@dpelisek - A typical solution would be to add a rownumber to each row partitioned by the column(s) you want to group on and only select the ones where the rownumber equals 1. I have setup an [SQL Fiddle](http://www.sqlfiddle.com/#!6/b1588/3) to show the intent. **Edit** , I really should read other answers first. Remi posted something similar. – Lieven Keersmaekers Feb 14 '14 at 06:44
3

This is wrong. The solution finds the MIN and not the FIRST. The comment here above is much better. – Matte Jan 08 '16 at 15:16
1

@Matte - True but the order doesn't matter. OP should have restated the question. Any arbitrary value of b will do *(for OP as per comments)* – Lieven Keersmaekers Jan 08 '16 at 21:18

2 Answers2