Query to return first result of a column in SQL?

Question

In SQL, is there a simple way to return all the rows containing the top instance of a value in a column?

Given the table below...

Size    Color    Price
S       Red      1
S       Green    4
S       Blue     7
M       Olive    45
M       Black    99
M       Orange   153
L       Purple   4
L       Red      75
L       Blue     98

I want to return...

Size    Color    Price
S       Red      1
M       Olive    45
L       Purple   4

Please tag which DBMS you are using (`SQL Server`, `Oracle`, etc.) as this affects our ability to answer — artemis, Oct 10 '19 at 19:44
"Top" is only meaningful if you specify an order. As it stands, it's not clear which of the values you want. — Joe, Oct 10 '19 at 19:46
@j8d in which case you'll get "S Green" in your result, not "S Red" as you have stated. But if that's what you want, Jerry M's answer is one way of doing it - at least you would have if you hadn't changed the values while I was writing this :) — Joe, Oct 10 '19 at 19:51
Your data and your expected results keep changing @j8d so I'm unable to post an example that definitely works until they stay the same. But my answer below should work regardless... Please test it. — artemis, Oct 10 '19 at 19:52
You also mention assume `price is descending order`, but your results show _ascending_ order, so which would you like? — artemis, Oct 10 '19 at 19:53

artemis · Accepted Answer · 2019-10-10T20:02:19.917

3

You can try a CTE function, and ROW_NUMBER(), to accomplish this:

This accomplishes it as descending which you say in the comments

;WITH CTE AS(
    SELECT myTable.*
    , RN = ROW_NUMBER()OVER(PARTITION BY Size ORDER BY Price DESC)
    FROM myTable 
)
SELECT Size, Color, Price FROM CTE
WHERE RN = 1

This accomplishes your desired results:

;WITH CTE AS(
    SELECT myTable.*
    , RN = ROW_NUMBER()OVER(PARTITION BY Size ORDER BY Price ASC)
    FROM myTable 
)
SELECT Size, Color, Price FROM CTE
WHERE RN = 1
ORDER BY
  CASE Size
    WHEN 'S' THEN 1
    WHEN 'M' THEN 2
    WHEN 'L' THEN 3
  END

An example SQL FIDDLE is here so you can see a demo.

Influenced from: Select the first instance of a record

edited Oct 10 '19 at 20:02

answered Oct 10 '19 at 19:46

artemis

6,857
11
46
99

This seems to work, but the CTE isn't really recursive. ;-) +1 – Carsten Massmann Oct 10 '19 at 20:01
You're right -- I have written "recursive CTE" so many times this month I did it automatically. – artemis Oct 10 '19 at 20:02
Good job , very usefull :) – sayah imad Oct 10 '19 at 22:42

Himanshu · Answer 2 · 2019-10-10T20:14:35.877

-1

You require to partition your data to get rownums assigned to each partitioned size and then whichever is the first partition thats gonna be the result as below

     Select size, color, price from
         (Select Size, Color,Price, 
         row_number() 
        over (partition by size order by 
             Price) as rn
       from mytable) t where t.rn =1

edited Oct 10 '19 at 20:14

answered Oct 10 '19 at 19:51

Himanshu

3,830
2
10
29

Updated. Windowed functions with partition clause especially arent applicable in having as they replicate the group by having the reason i had got previous query as wrong. Thanks though. – Himanshu Oct 10 '19 at 20:19

Query to return first result of a column in SQL?

2 Answers2