CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
pstanton
pstanton

Reputation: 36640

select first in group query

Apologies if this has been answered previously.

Say I have the following table:

colA | colB | colC
-----|------|-----
a    | 1    | 9
a    | 2    | 8
b    | 1    | 4
b    | 2    | 3
b    | 1    | 3
c    | 5    | 1
c    | 4    | 2

and I want to select the first row from each group (of column a, when sorted by column b,c asc)

colA | colB | colC
-----|------|-----
a    | 1    | 9
b    | 1    | 3
c    | 4    | 2

What is the tidiest way to structure the query to get these results?

(Postgresql 9)

Thanks, P.

Upvotes: 0

Views: 265

Answers (3)

pstanton
pstanton

Reputation: 36640

Besides "mu is too short"'s answer using window functions (which a college also turned me on to) I found that i can achieve the functionality using the much simpler "distinct on" feature:

select distinct on (colA) * from tbl order by colA, colB, colC;

Upvotes: 3

supersong
supersong

Reputation: 3

I used to have the same problem as you.I used a window function and a derived table then solve the problem.

Upvotes: 0

mu is too short
mu is too short

Reputation: 434665

You could use a window function and a derived table:

select cola, colb, colc
from (
    select cola, colb, colc,
           rank() over (partition by cola order by colb, colc asc) as r
    from your_table
) as dt
where r = 1

Window functions are pretty handy and should be in everyone's SQL toolkit.

Upvotes: 1

Related Questions