CODEWITHSUNDEEP
sqlsql-servert-sql
Jakub Klawon
Jakub Klawon

Reputation: 21

Selecting rows based on minimum value of one column

I need to select row with distinct value of column A and with minimal value of Order column in MSSQL DB.

Each record has more columns on the right hand side. I know I should used GROUP BY, but it does throw warnings, when I want to keep the right hand side columns a well.

Data set example:

A       | Order |  Multiple columns ...   |
--------+-------+-------------------------+
A1      |  3    |  ...                    |
A1      |  7    |  ...                    |
A2      |  2    |  ...                    |
A3      |  2    |  ...                    |
A3      |  8    |  ...                    |

So that I want to get these results:

A       | Order |  Multiple columns ...   |
--------+-------+-------------------------+
A1      |  3    |  ...                    |
A2      |  2    |  ...                    |
A3      |  2    |  ...                    |

The query I tried to use and throws warning is this one:

SELECT A, MIN(Order), Column B, Column C, Column D...
    FROM Table
    GROUP BY A
    ORDER BY A

Upvotes: 2

Views: 2822

Answers (3)

Kennah
Kennah

Reputation: 487

If your dataset isn't so large that a CTE is out of the question.

; WITH
    CTE1 AS
    (
        SELECT
            A
            , Order
            , B
            , C
            , D
            , RowNumber = ROW_NUMBER() OVER (PARTITION BY A ORDER BY Order ASC)
        FROM Table
    )

SELECT
    A
    , Order
    , B
    , C
    , D
WHERE (RowNumber = 1)

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You could also use top (1) with ties

select top (1) with ties a, * 
from table t
order by row_number() over (partition by a order by [Order])

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Probably the most efficient method (with the right indexes) is:

select t.*
from t
where t.order = (select min(t2.order) from t t2 where t2.a = t.a);

The more common approach is to use row_number(), which I also recommend.

Upvotes: 4

Related Questions