CODEWITHSUNDEEP
sqlsql-server-2012
FNR
FNR

Reputation: 499

SQL - Only one result per set

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.

Upvotes: 2

Views: 78

Answers (2)

ngrashia
ngrashia

Reputation: 9894

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

Upvotes: 2

Rowland Shaw
Rowland Shaw

Reputation: 38130

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

Upvotes: 0

Related Questions