SQL Server query to select the highest quantity data row

Question

I have been trying to find a similar case. I found a lot, but I still can't figure it out to adopt to my query.

I have a testDB in SQL Server that has 3 tables, as shown in picture below:

Table Structure

I created query as below:

SELECT P.FirstName,
        P.LastName,
        O.ProductType, 
        PO.ProductName,
        PO.Quantity 
FROM Persons AS P
INNER JOIN Orders AS O ON P.PersonID = O.PersonID 
INNER JOIN ProductOrders AS PO ON PO.OrderID = O.OrderID;

Current result, it shows all records from ProductOrders. See picture below:

Current Result

I want the result that only shows, for each Person name only record with the highest quantity. My expected result as shown in picture below:

Expected Result

Thanks very much for your help.

Your first picture is wrong. The persons table doesn't have a productid. The order table doesn't either, so no foreign key on that of course. The product order table doesnt have a foreign key on product and order, but two foreign keys one for each column. Better delete that image; it's not helpful. And as to images in general: Please don't use them if not absolutely needed to show your issue. Paste the tables as text instead, so we can copy & paste, can even see them even when we can't see pictures, etc. — Thorsten Kettner
– Thorsten Kettner, Commented Mar 9, 2018 at 16:01
Your description is also wrong: "I want the result only shows, for each Person name only record with the highest Quantity." Obviously you don't want the product with the highest quantity per person, but per person and product type (otherwise each name would appear only once in your results). — Thorsten Kettner
– Thorsten Kettner, Commented Mar 9, 2018 at 16:10

Gordon Linoff · Accepted Answer · 2018-03-10 02:41:04Z

3

SQL Server has the TOP WITH TIES/ROW_NUMBER() trick that does this very elegantly:

SELECT TOP (1) WITH TIES P.FirstName, P.LastName, O.ProductType, PO.ProductName, PO.Quantity 
FROM Persons P INNER JOIN
     Orders O
     ON P.PersonID = O.PersonID INNER JOIN
     ProductOrders PO
     ON PO.OrderID = O.OrderID
ORDER BY ROW_NUMBER() OVER (PARTITION BY P.PersonId, P.ProductType ORDER BY PO.Quantity DESC);

edited Mar 10, 2018 at 2:41

answered Mar 9, 2018 at 15:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Thorsten Kettner Over a year ago

That should be (PARTITION BY P.PersonId, O.ProductType ORDER BY PO.Quantity DESC).

Harzio Over a year ago

Thanks Gordon. I try your query, but I got an error "Incorrect syntax near the keyword 'DESC'." Not sure what is incorrect.

Harzio Over a year ago

Updated: Follow @ThorstenKettner comment will solve my the "Incorrect syntax near the keyword 'DESC'." error. Thanks very much everyone.

Harzio Over a year ago

@Gordon, if I want to final result to Sort by P.FirstName, is it possible? Thanks

Gordon Linoff Over a year ago

@Harzio . . . If you wanted to do that, you would need a subquery or CTE.

cloudsafe · Accepted Answer · 2018-03-09 16:12:27Z

0

Use Window functions:

SELECT distinct P.FirstName
            , P.LastName
            , O.ProductType
            , first_value(O.ProductName) OVER (Partition By P.FirstName, P.LastName, O.ProductType Order by PO.Quantity desc) as [Productname]
            , max(PO.Quantity) OVER (Partition By P.FirstName, P.LastName, O.ProductType) as [Quantity]
FROM Persons AS P 
INNER JOIN Orders AS O ON P.PersonID = O.PersonID 
INNER JOIN ProductOrders AS PO ON PO.OrderID = O.OrderID;

edited Mar 9, 2018 at 16:12

answered Mar 9, 2018 at 15:53

cloudsafe

2,5241 gold badge11 silver badges27 bronze badges

1 Comment

Harzio Over a year ago

Thanks very much, @cloudsafe. I run your query and it does what I want.

Collectives™ on Stack Overflow

SQL Server query to select the highest quantity data row

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related