CODEWITHSUNDEEP
sqlsql-server
Marcel Marino
Marcel Marino

Reputation: 1002

SQL - Select top item from a grouping of two columns

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

Upvotes: 0

Views: 54

Answers (4)

Matt
Matt

Reputation: 15061

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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;

Upvotes: 1

Anand
Anand

Reputation: 1123

A much simpler way to do this:

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

Upvotes: 2

Marcel Marino
Marcel Marino

Reputation: 1002

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.

Upvotes: 0

Related Questions