CODEWITHSUNDEEP
sqlmysql
chadgh
chadgh

Reputation: 9773

Returning the DISTINCT first character of a field (MySQL)

I would like to produce a character list of all of the first letters of column in my database. The SQL below illistrats what I would like to return.

SELECT DISTINCT first_character(name) FROM mydatabase

Is there a way to do this in MySQL?

EDIT What is the advantage of using SUBSTRING over LEFT and vice versa?

EDIT Currently there are about 1700 records in the table and growing.

Upvotes: 28

Views: 47273

Answers (3)

chadgh
chadgh

Reputation: 9773

Sorry to do this, but I figured out exactly what I needed to do just now.

SELECT DISTINCT LEFT(name, 1) FROM table;

This returned a list of the first, distinct, single characters that each row in the column started with. I changed it to the following to get the list in alphanumeric order:

SELECT DISTINCT LEFT(name, 1) AS letter FROM table ORDER BY letter;

Works like a charm.

Upvotes: 84

Quassnoi
Quassnoi

Reputation: 425863

For your current table of 1,700 rows your solution is OK.

If you will have like 100,000 of rows, the DISTINCT may become inefficient.

Here's the article in my blog that shows how to do it efficiently:

This solution employs an index on name. It will jump over the index keys, selecting each first letter at most once.

First, you'll need to create a function:

CREATE FUNCTION fn_get_next_code(initial INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
        DECLARE _next VARCHAR(200);
        DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL;
        SELECT  ORD(SUBSTRING(name, 1, 1))
        INTO    _next
        FROM    t_names
        WHERE   name >= CHAR(initial + 1)
        ORDER BY
                name
        LIMIT 1;
        RETURN _next;
END

This function, given a code of a starting letter, returns the first starting letter next to the given from your table.

Second, use this function in a query:

SELECT  CHAR(@r) AS starting,
        @r := fn_get_next_letter(@r) AS next
FROM    (
        SELECT @r := ORD(LEFT(MIN(name), 1))
        ) vars, mytable
WHERE   @r IS NOT NULL

On each iteration, session variable @r will skip to the next starting letter using an index.

This will be very fast, but it pays for itself only if you have hundreds of thousands of rows.

Otherwise just use DISTINCT.

Upvotes: 10

Andomar
Andomar

Reputation: 238296

Sounds simple:

select distinct substring(field,1,1) as char
from mytable

Upvotes: 10

Related Questions