Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
And after the rows that start with D, how the next rows are to be ordered? –  ypercube Mar 4 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 at 13:33

2 Answers 2

up vote 5 down vote accepted

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)
share|improve this answer
    
Thank you for this example. I had no idea I was able to use CASE with ORDER BY. –  Ryan Mar 5 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 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 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 at 21:14

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; 
share|improve this answer
    
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 at 13:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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