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?

CodePudding user response：

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  |
 ------ ----------- 

CodePudding user response：

You can do it using row_number:

select nombre
from (
  select 
    row_number() over (partition by left(nombre, 1) order by nombre asc) as rn,
    nombre
  from names
  ) n
where rn = 1

You can test on this db<>fiddle

CodePudding user response：

with main as (
select 
*, 
row_number() over(partition by left(column,1) order by column) as rank_
)
select column where rank_ = 1

Steps:

• column = your actual column name

• First we are creating a CTE that does two things. It creates a rank using the windows function row_number(). This rank is build using the following logic: Pick the column's first letter i.e the use of LEFT() function(1 specifies the placement i.e the first letter in our case). This will eventually tell SQL to pick every word's first letter. The second functionality is the ORDER BY within the window function.

• The ORDER BY orders all the words based on the A-Z i.e Ascending order by default. If you use DESC it will return the last word for the same starting character.

• Connecting both logics, what we get is the rank based on the ascending order of each word and the first character. [All the first words starting with each alphabet in a given data set]

• In the final select we just filter the rank_ = 1 in the where clause

• If you want the last word you can just use order by column DESC

IF windows function is not supported you can use the following solution, the logic remains almost the same:

with main as (
select
column,
left(column,1) as first_letter_per_word
from [table_name]
)

select
first_letter_per_word,
min(column) as word
from main 
group by 1  -- by default it is ASC