-1

I have the following query to get iPhones from table1 by color and limited to 10

SELECT * FROM table1 WHERE color = 'black' LIMIT 10

The above code works perfectly, When the black iPhones in table1 less than 10 I want to complete the iPhone's number to 10 with red color which I have also in table1 in one query

Dharman
  • 30,962
  • 25
  • 85
  • 135

1 Answers1

1

You can filter on both colors, and do a conditional sort:

SELECT * 
FROM table1 
WHERE color in ('black', 'red')
ORDER BY color = 'red'
LIMIT 10

Condition color = 'red' yields 1 when fulfilled, else 0 - so this actually puts 'black' first.

You can also use field() here:

SELECT * 
FROM table1 
WHERE color in ('black', 'red')
ORDER BY FIELD(color, 'black', 'red')
LIMIT 10
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thanks it's working.. I'm wondering if there is any other way to do the same work with if statement or something like that –  May 06 '20 at 21:54
  • @EmmaGrove: well, you could do `ORDER BY IF(color = 'black', 0, 1)'`... But the above expressions are simpler. – GMB May 06 '20 at 21:55