CODEWITHSUNDEEP
sql-server-2012
Blue high Mountain
Blue high Mountain

Reputation: 67

SQL that returns highest volume and lowest price

I have a table like this:

CREATE TABLE customerQuote(
                           quoteID char(36) NOT NULL PRIMARY KEY
                          ,customerID char(36) NOT NULL
                          ,quoteNo INT
                          ,volume int
                          ,price decimal(6,2)
                           );

Now I need a query that returns customerID, quoteNo, highest volume and the lowest price for the highest volume.

An example: A customer (customerID) has quotes: quoteNo=55 and quoteNo=62

Those quotes have these entries:

quoteNo=55; volume=90; price=1.52
quoteNo=55; volume=25; price=1.65
quoteNo=55; volume=90; price=1.50
quoteNo=62; volume=99; price=1.40

For this particular customer I want the query to return:

customerID, 55, 90, 1.50
customerID, 62, 99, 1.40

Yes, I know the system is messy as it allows multiple entries for the same customer with the same volume, but that is real life, and the reason I need this query.

Upvotes: 0

Views: 36

Answers (1)

iamdave
iamdave

Reputation: 12243

You can achieve this with a derived table:

with mv   -- The derived table finds the quote with the highest volume.
as
(
    select customerID
            ,quoteNo
            ,max(volume) as maxVolume
    from customerQuote
    group by customerID
            ,quoteNo
)
select c.customerID      -- Which you then use to find the lowest price at that volume.
        ,c.quoteNo
        ,mv.maxVolume
        ,min(c.price) as minPrice
from customerQuote c
    inner join mv
        on c.customerID = mv.customerID
            and c.quoteNo = mv.quoteNo
            and c.volume = mv.maxVolume
group by c.customerID
        ,c.quoteNo
        ,mv.maxVolume;

Upvotes: 1

Related Questions