Question 20778 in Mysql Question posted in
The official MySQL documentation can be found here.

How does select query works – Mysql

I need to return value in preference order, if the value starts with a it should be returned first, if it does not exist we return the value which starts with b.

SELECT value FROM table one 
WHERE value LIKE a% OR value LIKE b% 
OR value LIKE c% OR value LIKE d% LIMIT 1;

Does the query return the value in order, as in the value which starts with ‘a’ should be returned and if a value does not exist then the value starting with ‘b’ should be returned and so on.

Tags:

2

Answers


  1. 0

    The stuff in your WHERE clause does not determine the order of rows in your result set. Unless your query includes an ORDER BY clause the order of rows is unpredictable. The server is free to return the rows to you in any convenient order, depending on who knows what?

    Many programmers fall into a trap here. We experiment with writing the query different ways on tiny test datasets and see different orders, so we get lulled into thinking the order is predictable. Then, our code goes into production on big, and hopefully growing data sets, and BAM! suddenly the server starts using a different query plan because the data grew, and the rows come back in a different order, and some program starts to fail. Usually in the middle of the night.

    It would be great if servers returned rows in random order when we don’t give an ORDER BY clause. That way we’d have a chance of catching this kind of bug in testing.

    If you find yourself writing a query with no ORDER BY, be afraid. Stop and think about unpredictable ordering.

    Login or Signup to reply.
  2. 0

    Try with something like this

    SELECT *
FROM [Table]
ORDER BY 
CASE 
WHEN [Value] like 'a%' THEN  1
WHEN [Value] like 'b%' THEN  2
WHEN [Value] like 'c%' THEN  3
ELSE 99 END
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search