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:

BeacherAckleyAddisonCaldwellCameronAlcottCarlingBeardsleyBeldon

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

Alphabetically sorted the list above is like this:

AckleyAddisonAlcottBeacherBeardsleyBeldonCaldwellCameronCarling

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

So I want the following output:

AckleyBeacherCaldwell

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

Best Answer

You can do it using row_number:

select nombrefrom (select row_number() over (partition by left(nombre, 1) order by nombre asc) as rn,nombrefrom names) nwhere rn = 1

You can test on this db<>fiddle

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 (selectcolumn,left(column,1) as first_letter_per_wordfrom [table_name])selectfirst_letter_per_word,min(column) as wordfrom main group by 1 -- by default it is ASC

Accepted Answer

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

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

Best Answer

Random Posts