6

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?

Alan M.
  • 1,309
  • 2
  • 19
  • 29
  • 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. Nov 20 '10 at 19:36

5 Answers5

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)
Sparky
  • 14,967
  • 2
  • 31
  • 45
  • 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. Nov 20 '10 at 19:16
  • 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. – Alan M. Nov 26 '10 at 16:01
1

This is working properly

(select * from toys where color = 'Yellow' LIMIT 1)
UNION ALL
(select * from toys where color = 'White' LIMIT 1)
namal
  • 1,164
  • 1
  • 10
  • 15
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)

Filipe YaBa Polido
  • 1,656
  • 1
  • 17
  • 39
  • 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. – Alan M. Nov 20 '10 at 19:22
0

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
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
-1

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

riotera
  • 1,613
  • 9
  • 14
  • 2
    The OP asks: _"Is it possible to do this fully in MySQL without walking through the array in PHP?"_ – jwueller Nov 20 '10 at 01:49
  • I didn't suggest to walking through, I suggested to merge – riotera Nov 20 '10 at 01:53
  • 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. – jwueller Nov 20 '10 at 01:58
  • @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. – Alan M. Nov 20 '10 at 19:18