CODEWITHSUNDEEP
sqlgreatest-n-per-group
Geek
Geek

Reputation: 85

Find lowest value in particular group

My Question is:

My database has 3 columns and has entries like this:

1|Tom   |1
2|Lucy  |1
3|Frank |2
4|Jane  |3
5|Robert|3

Now I want output like this:

1|Tom   |1
3|Frank |2
4|Jane  |3

Which means in each group(in this case third column) the lowest entry of first column.

Can anyone help?

Upvotes: 2

Views: 112

Answers (1)

MPelletier
MPelletier

Reputation: 16657

What you need is a correlated subquery with a group by.

One way to do this which is easy to follow is:

SELECT column1, name, column2
FROM MyTable as mt1
WHERE column1 in (SELECT Min(column1) FROM MyTable as mt2 GROUP BY column2)

But a better, cleaner way:

SELECT column1, name, column2
FROM MyTable as mt1
INNER JOIN
(SELECT Min(column1) as minc1 FROM MyTable as mt2 GROUP BY column2) as mt2
ON mt1.column1=mt2.minc1;

SQLFiddle

Note: These two forms should be supported by most DBMS's.

Upvotes: 4

Related Questions