By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,464 Members | 1,608 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,464 IT Pros & Developers. It's quick & easy.

Select statement

P: n/a
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
Share this Question
Share on Google+
6 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.