当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server 2012 - 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.

网友答案:

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
网友答案:

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
分享给朋友:
您可能感兴趣的文章:
随机阅读: