Sort alphabetically and get only first of every letter of the alphabet

Question

I have a MySQL table called names with a column names name.

This column has the following values:

Beacher
Ackley
Addison
Caldwell
Cameron
Alcott
Carling
Beardsley
Beldon

I want to get, alphabetically sorted, the value of every first letter.

Alphabetically sorted the list above is like this:

Ackley
Addison
Alcott
Beacher
Beardsley
Beldon
Caldwell
Cameron
Carling

The first value starting with A is Ackley, the first value starting with B is Beacher …

So I want the following output:

Ackley
Beacher
Caldwell

Is this even possible with a single SQL query? How?

Answer 1

The answers that use the row_number() window function require that you have MySQL 8.0.

Here's a solution that should work on MySQL 5.x.

select left(name, 1) as c, min(name) from names group by c;
+------+-----------+
| c    | min(name) |
+------+-----------+
| A    | Ackley    |
| B    | Beacher   |
| C    | Caldwell  |
+------+-----------+