homepage Welcome to WebmasterWorld Guest from 107.22.37.143
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
group by / order by preserving sort
enotalone




msg:3556442
 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.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved