CODEWITHSUNDEEP
mysqlselectlimit
Alan M.
Alan M.

Reputation: 1369

MySQL Query with multiple LIMITS

Let's say I have the following table with hundreds of toys of various colors...

---------------------------
  ITEM  |  COST  |  COLOR
---------------------------
    1   |  12.00 |  BLUE
    2   |  10.98 |  RED
    3   |   9.34 |  BLUE
    4   |  11.33 |  WHITE
    5   |   8.00 |  YELLOW
    .   |    .   |    .
    .   |    .   |    .
    .   |    .   |    .

If I wanted to select the three lowest priced blue toys, I could of course write...

SELECT * FROM TOYS WHERE Color = 'BLUE' ORDER BY COST LIMIT 3;

But, is there a way that I can select the a uniquely specified number of lowest priced yellow, white, and blue toys, as if I were saying...

 SELECT * FROM TOYS WHERE COLOR = 'YELLOW' (LIMIT 1) AND COLOR = 'WHITE' (LIMIT 3) AND COLOR = 'BLUE' (LIMIT 2) ORDER BY COST

Is it possible to do this fully in MySQL without walking through the array in PHP?

Upvotes: 6

Views: 9802

Answers (5)

Lukasz Szozda
Lukasz Szozda

Reputation: 175566

ROW_NUMBER() could be used:

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY COLOR ORDER BY COST) AS RN
      FROM TOYS
      WHERE COLOR IN ('YELLOW', 'WHITE', 'BLUE')) sub
WHERE RN <= CASE COLOR WHEN 'YELLOW' THEN 1 WHEN 'WHITE' THEN 3 WHEN 'BLUE' THEN 2 END

Upvotes: 1

namal
namal

Reputation: 1274

This is working properly

(select * from toys where color = 'Yellow' LIMIT 1)
UNION ALL
(select * from toys where color = 'White' LIMIT 1)

Upvotes: 2

Filipe YaBa Polido
Filipe YaBa Polido

Reputation: 1674

Well, IMHO, you were given 2 options here, however, I'll still go by merging the arrays as it won't take so many time/system resources like UNION. (Based on that you've said the table has a LOT of rows)

Upvotes: 0

Sparky
Sparky

Reputation: 15085

Why not?

select * from toys where color = 'Yellow' (LIMIT 1)
UNION
select * from toys where color = 'White' (LIMIT 3)
UNION
select * from toys where color = 'Blue' (LIMIT 2)

Upvotes: 9

riotera
riotera

Reputation: 1613

For me the solution is create 3 different queries and merge the 3 arrays in PHP

Upvotes: -1

Related Questions