MYSQL - Sort by first letter

Question

i have this sql query (below) and it works great but i need to modify it to select only records with $a first character. I've tried a few variations of the LIKE A% with no luck. The crux of my situation seems to be the name alias. If i use WHERE name LIKE 'A%' before the ORDER i get an error. That seems like the logical place for it to be. Any suggestions?

SELECT  
  IF(company_name <> '', company_name, PC_last_name) AS name, 
  customer_id AS id, 
  company_name AS cn, 
  PC_first_name AS pcf, 
  PC_last_name AS pcl, 
  primary_phone 
FROM sales_customer 
ORDER BY name

score 8 · Answer 1 · edited Feb 02 '14 at 12:55

8

Try this, it get's the first letter from the name.

SELECT IF(company_name <> '', company_name, PC_last_name) AS name, customer_id AS id, company_name AS cn, PC_first_name AS pcf, PC_last_name AS pcl, primary_phone
FROM sales_customer
ORDER BY SUBSTRING(name, 1, 1) ASC

edited Feb 02 '14 at 12:55

jeffery_the_wind

17,048
34
98
160

answered Feb 28 '12 at 15:57

blaff

304
1
5

This returned the whole table with some sort of random sort. Again i think the alias was the problem. – Lawrence Gadette Feb 28 '12 at 16:07

score 3 · Answer 2 · answered May 16 '19 at 22:51

I found this on dba stackexchange: https://dba.stackexchange.com/questions/60137/mysql-is-it-possible-to-order-a-query-by-a-specific-letter-using-order-by

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)

score 3 · Accepted Answer · answered Feb 28 '12 at 16:00

3

I think you can't use the alias for this comparison on WHERE. Try this:

SELECT  
  IF(company_name <> '', company_name, PC_last_name) AS name, 
  customer_id AS id, 
  company_name AS cn, 
  PC_first_name AS pcf, 
  PC_last_name AS pcl, 
  primary_phone 
FROM sales_customer 
WHERE IF(company_name <> '', company_name, PC_last_name) LIKE 'A%'
ORDER BY name

answered Feb 28 '12 at 16:00

bfavaretto

71,580
16
111
150

Bingo! Worked perfect. I knew it had something to do with the alias any particular reason why it wouldn't work correctly with the alias? – Lawrence Gadette Feb 28 '12 at 16:06
From the MySQL manual, *apud* [this answer](http://stackoverflow.com/a/200203/825789): It is not allowable to refer to a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See See [Section B.1.5.4, “Problems with Column Aliases”](http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html). – bfavaretto Feb 28 '12 at 16:14
Thanks for the info on that! Now i know. – Lawrence Gadette Feb 28 '12 at 18:23

MYSQL - Sort by first letter

3 Answers3