Mysql Multiple Subquery Limit

Question

I have a table with the following columns:

playlistid 
trackname 
playstatus
likes
dislikes
created

playstatus can have the following values:0, 1, 2 and 4.

The sql query that Im using now is:

 SELECT * from playlist
 WHERE playlistid=$myplaylistid && playstatus=0
 ORDER BY (likes-dislikes) DESC, created ASC

Its worth to mention that there at all times only will be one row with playstatus = 1, one row with playlistid=3, but there will be multiple rows with playstatus = 0 and 2.

I want to change the query above so that I get 1 row from where playstatus=3, 1 row from where playstatus=1, and 3 rows from where playstatus=0 in one single query. All these have to be chosen based on the same orderby that you see in the query above.

All help is appreciated! Thank you for your time!

Naveen Kumar · Accepted Answer · 2012-03-20 12:20:29Z

1

Try this query

SELECT * from playlist WHERE playlistid=$myplaylistid && playstatus=1
ORDER BY (likes-dislikes) DESC, created ASC limit 1
union 
SELECT * from playlist WHERE playlistid=$myplaylistid && playstatus=3 
ORDER BY (likes-dislikes) DESC, created ASC limit 1
union 
SELECT * from playlist WHERE playlistid=$myplaylistid && playstatus=0
ORDER BY (likes-dislikes) DESC, created ASC limit 3

answered Mar 20, 2012 at 12:20

Naveen Kumar

4,6011 gold badge20 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AlexanderN Over a year ago

You forgot some parentheses, but other than that It worked like a charm. Since the other user didnt read the question correctly like you did, you get the V. (I needed to limit each playstatus, aswell as use orderby on each of them).

Naveen Kumar · Accepted Answer · 2012-03-20 12:25:31Z

1

Try this

SELECT * from playlist WHERE playlistid=$myplaylistid && playstatus=3
union 
SELECT * from playlist WHERE playlistid=$myplaylistid && playstatus=1
union 
SELECT * from playlist WHERE playlistid=$myplaylistid && playstatus=0
ORDER BY (likes-dislikes) DESC, created ASC

edited Mar 20, 2012 at 12:25

Naveen Kumar

4,6011 gold badge20 silver badges39 bronze badges

answered Mar 20, 2012 at 11:59

Neeraj

9,25318 gold badges63 silver badges91 bronze badges

Collectives™ on Stack Overflow

Mysql Multiple Subquery Limit

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related