Multiple Selection Query MySQL

Question

Is it possible to make a query which selects one record that has the player_type 'IF' and 10 records that has the player_type 'Gold' which is ordered by 'cardweight' field / rand() desc? Thanks. Below are some attached screenshots of my table showing of some the player_type 'IF' and 'Gold' records.

Records with player_type as 'IF'

Records with Player Type as 'Gold'

essentially, I would like to combine

SELECT * 
FROM  `my_players` 
WHERE  `player_type` =  'Gold'
ORDER BY cardweight / RAND( ) DESC 
LIMIT 0 , 10

and

    SELECT * 
FROM  `my_players` 
WHERE  `player_type` =  'IF'
ORDER BY cardweight / RAND( ) DESC 
LIMIT 0 , 1

in one query that can be ran at once if possible.

Pretty unclear what you're asking. Post some sample data and intended output — Gurwinder Singh
– Gurwinder Singh, Commented Dec 18, 2016 at 11:57
@GurwinderSingh I've tried to make it a bit clearer now sorry. — KmanOfficial
– KmanOfficial, Commented Dec 18, 2016 at 12:03
What are the fields that you want in each query output? are they same? — Gurwinder Singh
– Gurwinder Singh, Commented Dec 18, 2016 at 12:04
It should return all the information held in each record, but I just want it that it randomly selects 10 records with the player_type 'gold' and 1 with the record with the player type 'IF' and is selected randomly dependent on the cardweight field - e.g. ORDER BY cardweight/rand() desc — KmanOfficial
– KmanOfficial, Commented Dec 18, 2016 at 12:09
You should explain why the obvious solution of union all does or does not meet your needs. — Gordon Linoff
– Gordon Linoff, Commented Dec 18, 2016 at 13:21

Gurwinder Singh · Accepted Answer · 2016-12-18 12:11:23Z

1

You use union all:

(SELECT * 
FROM  `my_players` 
WHERE  `player_type` =  'Gold'
ORDER BY cardweight / RAND( ) DESC 
LIMIT 0 , 10)
UNION ALL
(SELECT * 
FROM  `my_players` 
WHERE  `player_type` =  'IF'
ORDER BY cardweight / RAND( ) DESC 
LIMIT 0 , 1)

answered Dec 18, 2016 at 12:11

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

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

Comments

Ferran · Accepted Answer · 2016-12-18 12:11:00Z

0

You can do a UNION:

SELECT * 
FROM  `my_players` 
WHERE  `player_type` =  'Gold'
ORDER BY cardweight / RAND( ) DESC 
LIMIT 0 , 10

UNION

SELECT * 
FROM  `my_players` 
WHERE  `player_type` =  'IF'
ORDER BY cardweight / RAND( ) DESC 
LIMIT 0 , 1

answered Dec 18, 2016 at 12:11

Ferran

1421 silver badge10 bronze badges

Collectives™ on Stack Overflow

Multiple Selection Query MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related