I am having little hard time with this task, would appreciate if anyone can help me out as to how it can be accomplished. Thanks.
fields: item_id, title, sale, store
item_id is the primary key
title is a varchar
sale is decimal
store is int
How would I get top 10 discounts (stored in the field "sale") while showing only 1 listing per store.
Now doing just
Select item_id, title, sale, store from items
group by store
order by sale desc
does not work because after the group by I get the first entry for each store even though it might not be the best discount from the store.
Also does not work
select item_id, title, store, sale, max(sale) as max
group by store id
order by max desc
any other way to do this without a subselect?
If this is not possible without some very slow subselect or a join I am thinking about just getting 15-20 rows without even grouping and they taking out the duplicates while looping through the resultset. I know more code, but might be much faster.