4

What I'd like to do is order my query so that the results show every row found, but are ordered by showing results that begin with the letter 'D' first. Is this something that is possible to do with ORDER BY? I know I can use WHERE field LIKE 'D%', but I'd like all the results not just those with D.

My query currently looks as follows:

SELECT pname, pdescription, price FROM products WHERE manufacturer = 3 ORDER BY pname ASC
  • And after the rows that start with D, how the next rows are to be ordered? – ypercubeᵀᴹ Mar 4 '14 at 22:01
  • I suppose the order wouldn't matter after D. Perhaps ASC alphabetically, but the only thing that has been requested of me is to ensure that I sort by the letter 'D'. – Ryan Mar 5 '14 at 13:33
8

Have a look at below example

DROP TABLE IF EXISTS products;

create table products(pname CHAR(30),pdescription CHAR(30),price DECIMAL(10,2),manufacturer CHAR(30));

INSERT INTO products VALUES
    ('Toys','These are toys',15.25,'ABC'),
    ('Dolls','These are Dolls',35.25,'PQR'),
    ('DustPan','These are DustPan',75.25,'AZD'),
    ('Doors','These are Doors',175.25,'RAZD'),
    ('TV','These are TV',11175.25,'RAZD'),
    ('Bed','These are Bed',1175.25,'ARAZD');

/** Check all data **/

SELECT * FROM products;
+---------+-------------------+----------+--------------+
| pname   | pdescription      | price    | manufacturer |
+---------+-------------------+----------+--------------+
| Toys    | These are toys    |    15.25 | ABC          |
| Dolls   | These are Dolls   |    35.25 | PQR          |
| DustPan | These are DustPan |    75.25 | AZD          |
| Doors   | These are Doors   |   175.25 | RAZD         |
| TV      | These are TV      | 11175.25 | RAZD         |
| Bed     | These are Bed     |  1175.25 | ARAZD        |
+---------+-------------------+----------+--------------+
6 rows in set (0.00 sec)

/** Order by D% **/
SELECT 
        pname, pdescription, price
    FROM
        products
    ORDER BY 
    CASE
        WHEN pname LIKE 'D%' THEN 1
        ELSE 2
    END;
+---------+-------------------+----------+
| pname   | pdescription      | price    |
+---------+-------------------+----------+
| Dolls   | These are Dolls   |    35.25 |
| DustPan | These are DustPan |    75.25 |
| Doors   | These are Doors   |   175.25 |
| Toys    | These are toys    |    15.25 |
| TV      | These are TV      | 11175.25 |
| Bed     | These are Bed     |  1175.25 |
+---------+-------------------+----------+
6 rows in set (0.00 sec)
  • Thank you for this example. I had no idea I was able to use CASE with ORDER BY. – Ryan Mar 5 '14 at 13:34
  • 2
    You can use any expression in the ORDER BY clause. You can also use ORDER BY CASE...END, pname; if you care about the secondary order of the words that start with D (should 'Dallas' be before 'Dolls' or you don't care?) and the rows that start with other letters. – ypercubeᵀᴹ Mar 5 '14 at 13:37
  • I suppose the other letters will not matter as long as it begins with D and shows before all results that do not begin with D. – Ryan Mar 5 '14 at 21:01
  • @ypercube I just now saw wher eyou said I could use END, pname; which I did and my client seems to be happy with the outcome. I appreciate that tip! – Ryan Mar 5 '14 at 21:14
  • This query returns all rows irrespective of searched term. Not only I get pname starting with D, but even all those which do not have D, only success is that results have rows which have starting letter as 'D' are on the top of the result. How can I avoid that? – Prashant Shrivastava Jun 22 '16 at 15:22
2

You can create a new field in the SELECT statement to specify that text like 'D%' is the preferred sort order then order by the new field.

Example:

  SELECT 
    pname
   , pdescription
   , price 
   , CASE WHEN pname LIKE 'D%' 
     THEN 1
      ELSE 2
     END AS sortpreference
FROM products
WHERE manufacturer = 3 
ORDER BY sortpreference; 
  • Thank you for your answer. I will give this a try when I get back to work later this afternoon and go from there. – Ryan Mar 5 '14 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.