CODEWITHSUNDEEP
sqlsql-server
let007live4ever
let007live4ever

Reputation: 43

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

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?

Upvotes: 1

Views: 3325

Answers (4)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115660

Another way:

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

Upvotes: 0

Chandu
Chandu

Reputation: 82953

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

Upvotes: 4

Roman Asanov
Roman Asanov

Reputation: 297

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

Upvotes: 1

Thomas
Thomas

Reputation: 64674

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

Upvotes: 3

Related Questions