CODEWITHSUNDEEP
mysql
Flo
Flo

Reputation: 1671

Mysql WHERE optional

if i have an example table like

id || value_a

I want to select 5 entries where value_a = 1. Quite simple so far, but now if there are less results than 5, i want to still have 5 results. So if there are 3 entries with value_a = 1, then I want to get those 3 results and 2 other ones where value_a can be anything. How can i achieve this in the best way?

Upvotes: 1

Views: 372

Answers (2)

Quassnoi
Quassnoi

Reputation: 425341

(
SELECT  *
FROM    mytable
WHERE   value_a = 1
LIMIT 5
)
UNION ALL
(
SELECT  *
FROM    mytable
WHERE   value_a <> 1
LIMIT 5
)
ORDER BY
        value_a = 1 DESC
LIMIT 5

This will use an index on value_a efficiently

Upvotes: 1

ceejayoz
ceejayoz

Reputation: 180004

You can order by the result of a comparison calculation, like so:

SELECT stuff FROM table ORDER BY (value_a = 'value you want') DESC LIMIT 5

Upvotes: 4

Related Questions