PHP + MySQL - Sorting Query

Question

I have a table with a column which can contain: "kldr", "bgg" or an integer.

Now i want a query with the result in the following order:

 1. kldr 
 2. kldr 
 3. bgg 
 4. bgg 
 5. 1  
 6. 2 
 7. 3
 8. etc.

Can anyone help me out? My current query is obvious not working.

SELECT * 
FROM table 
WHERE column_x='$value_x' 
ORDER BY column_y LIKE '%kldr%' ASC, LIKE '%bgg%' ASC, floor ASC

Giorgos Betsos · Accepted Answer · 2016-02-10 11:06:55Z

5

You can use a CASE expression in the ORDER BY clause:

SELECT * 
FROM table 
WHERE column_x='$value_x' 
ORDER BY CASE WHEN column_y LIKE '%kldr%' THEN 1
              WHEN column_y LIKE '%bgg%' THEN 2
              ELSE column_y + 2
         END

answered Feb 10, 2016 at 11:06

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ramon Bakker Over a year ago

Ah thanks. 'THEN' sets only the order position as i understand?

Giorgos Betsos Over a year ago

@RamonBakker THEN determines the number returned by the CASE expression.

Collectives™ on Stack Overflow

PHP + MySQL - Sorting Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related