SQL query to get max of a column and display with every row

Question

Here's my scenario... Multiple Items with multiple item types which have multiple prices. Want to select Items with all types showing the MAX price. Can't figure out how to get the max?

Input:

ProductId   ProductType Description     Price

1 A BAKED BEANS 1.29

1 B BAKED BEANS 1.98

Output:

ProductId   ProductType Description     Price

1 A BAKED BEANS 1.98

1 B BAKED BEANS 1.98

Any suggestions?

Chandu · Accepted Answer · 2011-04-12 18:27:34Z

4

Try this:

SELECT ProductId, 
    ProductType,
    Description,
    b.price
  FROM <YOUR_TABLE> a, 
        (SELECT MAX(price) price FROM <YOUR_TABLE>) b

For those who love ANSI syntax:

SELECT ProductId, 
    ProductType,
    Description,
    b.price
  FROM <YOUR_TABLE> a INNER JOIN 
    (SELECT MAX(price) price FROM <YOUR_TABLE>) b
   ON 1=1

edited Apr 12, 2011 at 18:27

answered Apr 12, 2011 at 18:21

Chandu

83.2k19 gold badges136 silver badges136 bronze badges

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

2 Comments

Thomas Over a year ago

Although, I would never recommend using the comma-separated syntax. The day will come when SQL Server will stop supporting it (if only it could happen sooner).

Chandu Over a year ago

@Thomas: Updated the post with ANSII version of join

Thomas · Accepted Answer · 2011-04-12 18:19:21Z

3

Select ProductId, ProductType, Description, MaxByDesc.MaxPrice
From Product
    Join    (
            Select Description, Max(Price) As MaxPrice
            From Product 
            Group By Description
            ) As MaxByDesc
        On MaxByDesc.Description = Product.Description

If you are using SQL Server 2005 or later:

Select ProductId, ProductType, Description
    , Max( Price ) Over ( Partition By Description ) As MaxPrice
From Product

answered Apr 12, 2011 at 18:19

Thomas

64.8k12 gold badges98 silver badges142 bronze badges

1 Comment

let007live4ever Over a year ago

It works. Thanks so much! I am using ROW_NUMBER() in my subquery to grab newest Price. Didn't know that I could use MAX() with Partition BY...

Roman Asanov · Accepted Answer · 2011-04-12 18:27:32Z

1

Thomas, right, except for that you need to group by type:

Select ProductId, ProductType, Description, MaxByDesc.MaxPrice
From Product
Join    (
        Select Description, Max(Price) As MaxPrice
        From Product 
        Group By ProductType
        ) As MaxByDesc
    On MaxByDesc.ProductType = Product.ProductType

answered Apr 12, 2011 at 18:27

Roman Asanov

2972 silver badges7 bronze badges

Comments

ypercubeᵀᴹ · Accepted Answer · 2011-04-12 19:51:52Z

0

Another way:

SELECT ProductId
     , ProductType
     , Description
     , ( SELECT MAX(price)
         FROM Product pg
         WHERE pg.Description = p.Description 
       ) AS MaxPrice
  FROM Product p

answered Apr 12, 2011 at 19:51

ypercubeᵀᴹ

116k19 gold badges181 silver badges247 bronze badges

Collectives™ on Stack Overflow

SQL query to get max of a column and display with every row

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related