By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 5.7.27 |
CREATE TABLE Table1
(`letters` varchar(1), `numbers` int)
;
INSERT INTO Table1
(`letters`, `numbers`)
VALUES
('a', 1),
('a', 2),
('a', 3),
('a', 4),
('b', 1),
('b', 2),
('b', 3),
('b', 4),
('c', 1),
('c', 2),
('c', 3),
('c', 4),
('d', 1),
('d', 2),
('d', 3),
('d', 4)
;
SELECT `numbers`,`letters` FROM
(SELECT `numbers`,
@curRank := @curRank + 1 AS rank
FROM Table1 t, (SELECT @curRank := 0) r
GROUP By `numbers`
ORDER BY `numbers`) NB1
INNER JOIN
(SELECT `letters`,
@curRank1 := @curRank1 + 1 AS rank
FROM (
Select `letters` FROM Table1 t
GROUP By `letters`) t2, (SELECT @curRank1 := 0) r
ORDER BY `letters`) LT1 ON NB1.rank = LT1.rank;
| numbers | letters |
|---|---|
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
SELECT `letters`,
@curRank1 := @curRank1 + 1 AS rank
FROM (
Select `letters` FROM Table1 t
GROUP By `letters`) t2, (SELECT @curRank1 := 0) r
ORDER BY `letters`
| letters | rank |
|---|---|
| a | 1 |
| b | 2 |
| c | 3 |
| d | 4 |
SHOW GLOBAL VARIABLES Like '%sql_mode%';
| Variable_name | Value |
|---|---|
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
SELECT `numbers`,
@curRank := @curRank + 1 AS rank
FROM Table1 t, (SELECT @curRank := 0) r
GROUP By `numbers`
ORDER BY `numbers`
| numbers | rank |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |