MySQL Query with multiple LIMITS

Question

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?

I am assuming the following would not work because it would chew up resources: Execute the complex query without the final WHERE clause (e.g., "COLOR" above); store the results in a temporary table; and then run a much simpler query against the temporary table? Since the website could potentially have hundreds, even thousands, of simultaneous users, there's a chance this query would be executed hundreds of times per second. Am I right in assuming that there's much more overhead in creating and dropping temp tables than having multiple complex queries? — Alan M.
– Alan M., Commented Nov 20, 2010 at 19:36

Sparky · Accepted Answer · 2010-11-20 01:48:15Z

9

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)

answered Nov 20, 2010 at 1:48

Sparky

15.1k2 gold badges34 silver badges45 bronze badges

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

2 Comments

Alan M. Over a year ago

I used the simple toy example to illustrate what I am trying to achieve. The real query, to which I need to apply the technique, has a few nested queries, joins, etc. It's quite complex.

Alan M. Over a year ago

Accepted this response because Sparky answered the question that I originally asked. I had not noted the complexity of the query in my original example.

namal · Accepted Answer · 2020-05-23 07:07:24Z

2

This is working properly

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

answered May 23, 2020 at 7:07

namal

1,2941 gold badge12 silver badges15 bronze badges

Comments

Lukasz Szozda · Accepted Answer · 2020-05-23 07:15:33Z

1

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

answered May 23, 2020 at 7:15

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Comments

Filipe YaBa Polido · Accepted Answer · 2010-11-20 01:58:41Z

0

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)

answered Nov 20, 2010 at 1:58

Filipe YaBa Polido

1,6742 gold badges17 silver badges39 bronze badges

1 Comment

Alan M. Over a year ago

My max row count is probably 50K. The main thing I'm trying to address is that the real query, unlike my example, is very complex, so I wanted to find a way to avoid executing it multiple times.

riotera · Accepted Answer · 2010-11-20 01:48:03Z

-1

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

answered Nov 20, 2010 at 1:48

riotera

1,6139 silver badges14 bronze badges

4 Comments

jwueller Over a year ago

The OP asks: "Is it possible to do this fully in MySQL without walking through the array in PHP?"

riotera Over a year ago

I didn't suggest to walking through, I suggested to merge

jwueller Over a year ago

Merging is essentially the same as walking through (a bit more specific, though). The OP clearly did not want to use PHP for this task.

Alan M. Over a year ago

@Elusive: Yes, thank you. As noted in my comment to Sparky, the real life query is more complex than my simple toy example, so I wanted to see whether there was a more efficient way in MySQL, itself.

Collectives™ on Stack Overflow

MySQL Query with multiple LIMITS

5 Answers 5

2 Comments

Comments

Comments

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related