471,337 Members | 939 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,337 software developers and data experts.

Select statement

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.

Feb 21 '06 #1
6 1451
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.

Feb 21 '06 #2
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

Feb 21 '06 #3
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
Feb 21 '06 #4
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

Feb 21 '06 #5
SELECT category, Max(price) AS HighestPrice, MAX(item) AS Item_ID
FROM tbl
GROUP BY category
If you want Item ID included...

Feb 21 '06 #6
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

Feb 21 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

21 posts views Thread by John Fabiani | last post: by
3 posts views Thread by Tcs | last post: by
1 post views Thread by microsoft.public.dotnet.languages.vb | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.