homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

group by / order by preserving sort

10+ Year Member

Msg#: 3556440 posted 12:46 am on Jan 24, 2008 (gmt 0)

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.


Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved