CODEWITHSUNDEEP
mysqlwhere-clause
David Rodrigues
David Rodrigues

Reputation: 12532

Priority condition in MySQL

I like to select a row by priority on MySQL. I don't know if it is possible, but see the example below:

id name
1  John
2  Doe
3  Mary
4  Jhonny

Supposing that I don't know the ID, but by a specific reason, I need select by priority: 3, 2, 1... On this case, the MARY will be selected (#3). But if I will need select by order 5, 2, 1, the DOE will be selected (#2).

Ok, I can do it by using IN(5, 2, 1). The problem is that if I don't have any results (like IN(5, 6, 7)), I need at least one row (don't matter what).

For instance: select 5, 6, 7... nothing found... then, select the first found (like JOHN).

It's possible?

Bye!

Edit: I just thought of this solution, but I do not know how fast it should be, but it works well. Accept a response that has a better benchmark for nobody lose.

ORDER BY FIND_IN_SET(`id`, '5,6,7') DESC

Upvotes: 1

Views: 2284

Answers (3)

Sadee
Sadee

Reputation: 3170

SELECT U.username, id
FROM tbluser U
ORDER BY FIND_IN_SET(`username`, 'Tester') DESC, username ACS

This priorities (gives first) the "Tester" and then sort it A-Z

Upvotes: 0

Dewfy
Dewfy

Reputation: 23614

At first you need remap ID column to order column, let's do it with help of special union (first column is you id, second is order instruction):

 SELECT 5 as ID, 1 as ord
 UNION ALL SELECT 2 , 2
 UNION ALL SELECT 1 , 3

Is it clear? if so - let's do full example:

SELECT m.* from MyTable m
    LEFT OUTER JOIN (
       SELECT 5 as ID, 1 as ord
       UNION ALL SELECT 2 , 2
       UNION ALL SELECT 1 , 3
    ) o ON m.ID = o.ID
ORDER BY  COALESCE(o.ord, 100)

Upvotes: 0

ajreal
ajreal

Reputation: 47321

SELECT *
FROM your_table
ORDER BY 
(CASE id 
WHEN 5 THEN 1
WHEN 6 THEN 2
WHEN 7 THEN 3
ELSE id+10
END)
LIMIT $some_limit;

The trick is make use on order by

So, the matching id of 5 will give the priority 1,
matching id of 6 will give the priority 2,
matching id of 7 will give the priority 3,
otherwise, least id will return

assuming you are using unsigned for id

Upvotes: 6

Related Questions