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.

table: items
fields: item_id, title, sale, store

field types:

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
limit 10

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
limit 10

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.

Thank you.