How do I find highest prices for each manufacturer in database

Posted on 2005-04-18
Last Modified: 2010-03-19
Hi, I have a table with the following columns:

1. ProductID
2. Price
3. Manufacturer

for example:

203    $500   Samson
454    $1000  Samson
100    $800    Ricardos
203    $1200  Ricardos
501    $50      Chambers

What I need to do is group the information in such a way that returns the top price (in descending order) for each manufacturer and the ProductID for that price - so in the above example, this would be returned:

$1200 203 Ricardos
$1000 454 Samson
$50     501 Chambers

Can anyone please help with the sql query? I have tried grouping, but once I get to add the ProductID column, I get all the results rather than only the top price for each Manufacturer in the table.

Question by:georgep7
    LVL 15

    Accepted Solution

    How about this

    Don't have SQL here so I am afraid that I cant test it for you beforehand

    This assumes that your table is called products so you will probably have to change it

    SELECT p.Price, p.ProductID, p.Manufacturer
    FROM products AS p
    WHERE p.Price=(SELECT MAX(price) FROM products p2 WHERE p2.Manufacturer = p.Manufacturer)
    ORDER BY p.Price DESC



    Author Comment

    Hi Scott, your query only returns one manufacturer - this is what I tried (putting your query exactly as written with my table names didn't work, so note the slight variation)

    SELECT tProducts.Price, tProducts.SerialNumber, tProducts.Manuf
    FROM tProducts
    WHERE Price=(SELECT MAX(Price) FROM tProducts WHERE Manuf = Manuf)
    LVL 28

    Expert Comment

    Colosseo's statement should work.  You forgot to put back the aliases of the tables (p and p2).  Its important that those are there.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now