distinct - MySQL SELECT INCREMENT

Question

Reading this post: MySQL SELECT increment counter I tried the solution by juergen d and was successful, except when I attempted to use DISTINCT on the selected column, it returns the primary key values for each row rather than an indexed value. The successful query:

SELECT city,
ROW_NUMBER() OVER() 
FROM addresses 
WHERE city IS NOT NULL AND city <> '' 
ORDER BY city;

City can be the same for multiple rows, so the desire is to return only distinct values with the ordered numerical column.

question from:https://stackoverflow.com/questions/66052471/mysql-select-increment

Answer 1

Use a subquery to get the distinct values, then use ROW_NUMBER() in the main query.

SELECT city, ROW_NUMBER() OVER ()
FROM (
    SELECT DISTINCT city
    FROM addresses
    WHERE city IS NOT NULL AND city <> ''
) AS x
ORDER BY city