SQL - Select top item from a grouping of two columns

Question

I have a list of numbers attached to two separate columns, and I want to just return the first "match" of the two columns to get that data. I got close with this answer, but it only works with one field. I need it to work with a combination of fields. About ten second before I was ready to post.

Here's an example table "Item":

Item    Color     Area
Boat    Red       1
Boat    Red       2
Boat    Blue      4
Boat    Blue      5
Car     Red       3
Car     Red       4
Car     Blue      10
Car     Blue      31

And the result set returned should be:

Item    Color     Area
Boat    Red       1
Boat    Blue      4
Car     Red       3
Car     Blue      10

score 2 · Answer 1 · answered Jan 06 '17 at 16:05

2

A much simpler way to do this:

select Item,
    Color,
    min(Area) as Area
from Item
group by Item
    Color

answered Jan 06 '17 at 16:05

Anand

1,165
10
18

score 2 · Answer 2 · answered Jan 06 '17 at 16:10

Just use the MIN function with a GROUP BY.

SELECT Item, Color, MIN(area) AS Area
FROM Item
GROUP BY Item, Color

Output:

Item    Color   Area
Boat    Blue    4
Boat    Red     1
Car     Blue    10
Car     Red     3

SQL Fiddle: http://sqlfiddle.com/#!9/46a154/1/0

score 1 · Answer 3 · answered Jan 06 '17 at 16:06

1

SQL tables represent unordered sets. Hence, there is no "first" of anything without a column specifying the ordering.

For your example results, the simplest query is:

select item, color, min(area) as area
from item i
group by item, color;

answered Jan 06 '17 at 16:06

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 4 · answered Jan 06 '17 at 16:03

About ten seconds before I was ready to post the question, I realized the answer:

WITH summary AS (
SELECT i.item + ':' + i.color,             
       a.area, 
       ROW_NUMBER() OVER(PARTITION BY i.item + ':' + i.color, 
                             ORDER BY i.item + ':' + i.color DESC) AS rk
  FROM Item i
  group by (i.item + ':' + i.color, i.Area)

SELECT s.* FROM summary s WHERE s.rk = 1

It's as simple as combining the two composite key fields into one field and grouping based on that. This might be a bit hackish so if anyone wants to suggest a better option I'm all for it.

SQL - Select top item from a grouping of two columns

4 Answers4