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?

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

Another way:

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