Selecting first row per group

Question

I have a table with three columns: A,B,C.

The values are:

+---+-----+----+
| A |  B  | C  |
+---+-----+----+
| 1 | -10 |  5 |
| 1 |   0 |  5 |
| 1 |  10 |  5 |
| 2 |  10 | 12 |
| 2 |   0 | 12 |
| 3 | -10 | 14 |
| 4 |   0 |  8 |
| 4 |  10 |  8 |
| 5 |   0 |  6 |
| 5 |   1 |  6 |
| 5 |  -5 |  6 |
+---+-----+----+

If I first order the data by column A, then column B, then column C (although I did make all column C values the same per column A value) how would I select the "first row" per column A?

So, this should result in:

+---+-----+----+
| A |  B  | C  |
+---+-----+----+
| 1 | -10 |  5 |
| 2 |   0 | 12 |
| 3 | -10 | 14 |
| 4 |   0 |  8 |
| 5 |  -5 |  6 |
+---+-----+----+

There is also a detailed answer at dba.stackexchange.com: [Retrieving n rows per group](http://dba.stackexchange.com/q/86415/57105) with comparison of different approaches. — Vladimir Baranov, Jun 20 '16 at 06:04
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:30

Quassnoi · Accepted Answer · 2012-06-07T11:01:41.200

SELECT  a, b, c
FROM    (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY a ORDER BY b, c) rn
        FROM    mytable
        ) q
WHERE   rn = 1
ORDER BY
        a

or

SELECT  mi.*
FROM    (
        SELECT  DISTINCT  a
        FROM    mytable
        ) md
CROSS APPLY
        (
        SELECT  TOP 1 *
        FROM    mytable mi
        WHERE   mi.a = md.a
        ORDER BY
                b, c
        ) mi
ORDER BY
        a

Create a composite index on (a, b, c) for the queries to work faster.

Which one is more efficient depends on your data distribution.

If you have few distinct values of a but lots of records within each a, the second query would be better.

You could improve it even more by creating an indexed view:

CREATE VIEW v_mytable_da
WITH   SCHEMABINDING
AS
       SELECT  a, COUNT_BIG(*) cnt
       FROM    dbo.mytable
       GROUP BY
               a

GO

CREATE UNIQUE CLUSTERED INDEX
       pk_vmytableda_a
ON     v_mytable_da (a)

GO

SELECT  mi.*
FROM    v_mytable_da md
CROSS APPLY
        (
        SELECT  TOP 1 *
        FROM    mytable mi
        WHERE   mi.a = md.a
        ORDER BY
                b, c
        ) mi
ORDER BY
        a

Got a slight problem, I am using this for code which is a function, returning a table... but it wont let me include the ORDER BY statement .... EDIT: It's ok I have used a procedure — intrigued_66, Jun 07 '12 at 11:52
@Porcupine: `ORDER BY` has no sense inside a function. You should append `ORDER BY` at the end of the query using the function: `SELECT * FROM myfunction(2) ORDER BY a`. Optimizer will push it into the query if it proves efficient. — Quassnoi, Jun 07 '12 at 11:57

score 1 · Answer 2 · edited Jun 20 '16 at 06:46

1

SELECT *
FROM
  (SELECT *,
          ROW_NUMBER() OVER (PARTITION BY Dealld
                             ORDER BY Price, Dealld) rn
   FROM DealOffers) q
WHERE rn = 1
ORDER BY Name

edited Jun 20 '16 at 06:46

ρяσѕρєя K

132,198
53
198
213

answered Jun 20 '16 at 06:28

Anil Kumar

11
2

2

Please edit with more information. Code-only and "try this" answers are discouraged, because they contain no searchable content, and don't explain why someone should "try this". We make an effort here to be a resource for knowledge. – abarisone Jun 20 '16 at 07:08

Selecting first row per group

2 Answers2

Linked

Related