Max aggregate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Max aggregate

Author

Topic

ckuo@kahluadesigns.com
Yak Posting Veteran

58 Posts

Posted - 2002-08-08 : 13:42:50

Hi,
I have the follwing table.

PRODUCTS TABLE

ProductID------InternalProductCode-------Price-----Name
1--------------101-----------------------100-------TestProduct
2--------------101-----------------------200-------TestProduct
3--------------101-----------------------300-------TestProduct
4--------------102-----------------------100-------TestProduct2

I would like to select ProductID, InternalProductCode, Name and the Max price where the InternalProductCode is the same. So my result would look like this.

ProductID------InternalProductCode-------MaxPrice-----Name
1--------------101-----------------------300-------TestProduct
2--------------101-----------------------300-------TestProduct
3--------------101-----------------------300-------TestProduct
4--------------102-----------------------100-------TestProduct2

What is wrong with this statement? Thanks

Select
ProductID,
InternalProductCode,
Name,
(Select Max(Price) From Products Where InternalProductCode = InternalProductCode) As MaxPrice,
From Products

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-08 : 13:55:44

You need to alias your table names so the subquery knows what you are talking about...


Select 
    ProductID, 
    InternalProductCode, 
    Name, 
    (Select
        Max(Price)
    From
        Products p2
    Where
        p.InternalProductCode = p2.InternalProductCode) As MaxPrice, 
From
    Products p

Jay White
{0}

Edited by - Page47 on 08/08/2002 13:56:48

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources