homepage Welcome to WebmasterWorld Guest from 54.167.138.53
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

    
Mysql - Selecting 3 best per condition
jaruba




msg:4134826
 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




msg:4134845
 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




msg:4134848
 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




msg:4134855
 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