SQL - Only one result per set

Question

I have a SQL problem (MS SQL Server 2012), where I only want one result per set, but have different items in some rows, so a group by doesn't work.

Here is the statement:

Select Deliverer, ItemNumber, min(Price)
From MyTable
Group By Deliverer, ItemNumber

So I want the deliverer with the lowest price for one item. With this query I get the lowest price for each deliverer.

So a result like:

DelA    12345    1,25
DelB    11111    2,31

And not like

DelA    12345    1,25
DelB    12345    1,35
DelB    11111    2,31
DelC    11111    2,35

I know it is probably a stupid question with an easy solution, but I tried for about three hours now and just can't find a solution. Needles to say, I'm not very experienced with SQL.

Take a look at the following: http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group — Sasse, Sep 29 '14 at 08:53

ngrashia · Accepted Answer · 2014-09-29T09:22:07.907

2

Just Add an aggregate function to your deliverer field also, as appropriate (Either min or max). From your data, I guess you need min(deliverer) and hence use the below query to get your desired result.

Select mIN(Deliverer), ItemNumber, min(Price)
From MyTable
Group By  ItemNumber;

EDIT:

Below query should help you get the deliverer with the lowest price item-wise:

SELECT TABA.ITEMNUMBER, TABA.MINPRICE, TABB.DELIVERER
FROM
  ( 
      SELECT ITEMNUMBER, MIN(PRICE) MINPRICE 
      FROM MYTABLE GROUP BY
      ITEMNUMBER
   ) TABA JOIN
   MYTABLE TABB
   ON TABA.ITEMNUMBER=TABB.ITEMNUMBER AND 
      TABA.MINPRICE = TABB.PRICE

edited Sep 29 '14 at 09:22

answered Sep 29 '14 at 08:50

ngrashia

9,869
5
43
58

You'll end up with DelA everytime (if item is delivered by delA let's say), even if the min(price) is not related to him. OP wants "the deliverer with the lowest price". – xlecoustillier Sep 29 '14 at 08:51
1

Wont work the way he wants. Se DelA and DelB as Suppliers and ItemNumber as ArticleId and you understand why this doesn't work. Or I'm missing the point. :p – Sasse Sep 29 '14 at 08:52
@X.L.Ant: I understand. But as per the test data, this would work.Will change the query though – ngrashia Sep 29 '14 at 08:54
@X.L.Ant: Query changed. This should help the OP. – ngrashia Sep 29 '14 at 08:59
@Sasse: I dont understand the comment. But please check if the edited query would work. – ngrashia Sep 29 '14 at 08:59
2

Add a `FROM` and this looks better :) – xlecoustillier Sep 29 '14 at 09:01
If I have understand the question correctly then you're still not taking the Deliverer into consideration. Maybe if you would add the Delivery in Group By clause and select clause and then in the join clause it would be correct. – Sasse Sep 29 '14 at 09:06
I think I didn't understand the question correct. I think your edited answer is correct. =) Only trouble is if multiple delivers have the same price, but the OP doesn't specify what he/she want to happend in a case like that. – Sasse Sep 29 '14 at 09:09
@Nishanthi Grashia I just tried your second query and as far as I can tell it works. Thanks a lot for your help :) @ Sasse I actually haven't thought about that. But I need one row per itemnumber, so I guess first come, first serve? – FNR Sep 29 '14 at 09:19
@FNR: You can consider accepting it as answer and upvoting if you have sufficient reputation – ngrashia Sep 29 '14 at 09:22
@Sasse: Yes I know, In that case, may be OP would have to decide which deliverer to use in case of multiple deliverers with same cost. – ngrashia Sep 29 '14 at 09:23

score 0 · Answer 2 · answered Sep 29 '14 at 09:08

You should be able to do this with the RANK() (or DENSE_RANK()) functions, and a bit of partitioning, so something like:

; With rankings as (
    SELECT      Deliverer,
                rankings.ItemNumber, 
                rankings.Price
                RANK() OVER (PARTITION BY ItemNumber ORDER BY Price ASC) AS Ranking

    FROM        MyTable (Deliverer, ItemNumber, Price)
)
SELECT      rankings.Deliverer, 
            rankings.ItemNumber, 
            rankings.Price

FROM        rankings

WHERE       ranking = 1

SQL - Only one result per set

2 Answers2

EDIT: