homepage Welcome to WebmasterWorld Guest from 54.166.96.101
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Mysql - Selecting 3 best per condition
jaruba

5+ Year Member



 
Msg#: 4134824 posted 6:14 pm on May 18, 2010 (gmt 0)

I have a table formed like this:

id | grp | mv | vt
------------------
1 | 1 | 2 | 8
2 | 1 | 4 | 3
3 | 2 | 3 | 5
4 | 1 | 6 | 10
5 | 2 | 1 | 34
6 | 3 | 9 | 1
7 | 1 | 10| 70
..

I'm kind of new to mysql.. and what I need to do is select 3 rows with ORDER BY vt DESC for each condition like grp='3' for example. Is there any way to do this with only one mysql query?

For grp='1' and grp='2', the result I'm hoping for would be:

id | grp | mv | vt
------------------
7 | 1 | 10| 70
4 | 1 | 6 | 10
1 | 1 | 2 | 8
5 | 2 | 1 | 34
3 | 2 | 3 | 5

Thank you in advance for any help.

 

Demaestro

WebmasterWorld Senior Member demaestro us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4134824 posted 6:51 pm on May 18, 2010 (gmt 0)

try this:

Select * from table where grp="3" order by vt desc limit 3

For more than one where clause it would be like this.

*****Execute as 1 statement*******
Select * from table where grp="1" order by vt desc limit 3
UNION
Select * from table where grp="2" order by vt desc limit 3
************END************

Play around with those and see if you are getting the desired results.

Post back if you need.

Demaestro

WebmasterWorld Senior Member demaestro us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4134824 posted 6:57 pm on May 18, 2010 (gmt 0)

FYI:

You can do more than 2 unions

Just make sure you have a UNION between each query.

Select * from table where grp="1" order by vt desc limit 3
UNION
Select * from table where grp="2" order by vt desc limit 3
UNION
Select * from table where grp="3" order by vt desc limit 3

This is the only way to limit each grp to 3 rows though and have it returned in 1 query.

jaruba

5+ Year Member



 
Msg#: 4134824 posted 7:06 pm on May 18, 2010 (gmt 0)

Thank you for the quick reply, this should be exactly what I needed.

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