0

A sample of my table is as follows:

id name  category year value seller
 1 item1 cat1     2000 1     1
 2 item1 cat1     2000 5     2
 3 item2 cat1     2000 2     2
 4 item1 cat1     2001 3     2
 5 item2 cat1     2001 1     1
 6 item5 cat2     2000 1     1
 7 item6 cat2     2000 4     2
 8 item5 cat2     2001 1     1
 9 item6 cat2     2001 5     2

I want to find the item with the highest value for each category and year (seller id is irrelevant - it just represents that the same product is actually sold by a different seller for a different price).

So the desired result would be:

 2 item1 cat1 2000 5
 4 item1 cat1 2001 3
 7 item6 cat2 2000 4
 9 item6 cat2 2001 5

Also, how could I find the top 5 per category and year and what if 2 items are the highest valued for a certain category-year?

I tried the following query but it's not what I'm actually looking for:

SELECT id, name, category, year, max(value) as value
FROM product_value
GROUP BY category, year, name
ORDER BY category, year ASC

This one retrieves the highest value of a product for each category-year.

Therefore:

2 item1 cat1 2000 5 2
3 item2 cat1 2000 2 2
4 item1 cat1 2001 3 2
5 item2 cat1 2001 1 1
6 item5 cat2 2000 1 1
7 item6 cat2 2000 4 2
8 item5 cat2 2001 1 1
9 item6 cat2 2001 5 2
ManosT32
  • 1
  • 1
  • So why is id 5 included in the (desired) result? – Strawberry Jul 13 '14 at 13:47
  • oops it was a mistake – ManosT32 Jul 13 '14 at 13:58
  • I dont see how it is same with "Retrieving the last record in each group" since I dont want to get the last record in each group, since my data is not sorted with the last having the maximum value... – ManosT32 Jul 13 '14 at 14:08
  • It's identical... SELECT x.* FROM my_table x JOIN ( SELECT category , year , MAX(value) max_value FROM my_table GROUP BY category , year ) y ON y.category = x.category AND y.year = x.year AND y.max_value = x.value;... see – Strawberry Jul 13 '14 at 14:13
  • Damn, you are right, I just read Eric's answer there and it makes more sense...thanks – ManosT32 Jul 13 '14 at 14:21

0 Answers0