Select statement

[Maya]

Hello guys,

I have table "Shopping" that has 3 columns (Item, Category, Price)
Is there a straight forward way in T-SQL to select the Item that has
the highest price for each category as below:

Item Category Price
-------- ----------- ----------
1 2 13.99
2 2 20.69
3 2 7.00
4 3 16.00
5 3 17.00

The select statement I'm after should return Item: 2 and 5

Thanks for your help,

Maya.

[KBuser]

SELECT MAX(Price), Category
FROM table
GROUP BY Category
Just off the top of my head, haven't tested and I'm nothing special
with sql.

[Jon Skeet [C# MVP]]

Maya wrote:

I have table "Shopping" that has 3 columns (Item, Category, Price)
Is there a straight forward way in T-SQL to select the Item that has
the highest price for each category as below:

<snip>

While I'm sure you *will* get an answer here at some point (and I'll
think about it myself and try to answer soon) your question would
really be more suitable in a SQL group.

Jon

[Frank Dzaebel]

Hi Maya,

I have table "Shopping" that has 3 columns (Item, Category, Price)
Is there a straight forward way in T-SQL to select the Item that has
the highest price for each category as below:
The select statement I'm after should return Item: 2 and 5

For example sth. like this:
"select top 2 * From dbo.Item order by price desc"
but this belongs to a SQL NG.
ciao Frank
--
Dipl.Inf. Frank Dzaebel [MCP/MVP C#]
http://Dzaebel.NET

[Jon Skeet [C# MVP]]

Frank Dzaebel wrote:

I have table "Shopping" that has 3 columns (Item, Category, Price)
Is there a straight forward way in T-SQL to select the Item that has
the highest price for each category as below:
The select statement I'm after should return Item: 2 and 5
For example sth. like this:
"select top 2 * From dbo.Item order by price desc"

No, because that doesn't distinguish by category. If the top two prices
are both for the same category, it'll just give those two.
but this belongs to a SQL NG.

Agreed.

Jon

[KBuser]

SELECT category, Max(price) AS HighestPrice, MAX(item) AS Item_ID
FROM tbl
GROUP BY category
If you want Item ID included...

[Maya]

Sorry about the wrong newsgroup, have posted here by mistake as I'm
working on c'# as the same time as well.

Anyway, got the answer from the SQL news group.

Apologies again...

Maya