0

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.

user3783243
  • 5,368
  • 5
  • 22
  • 41
Larry
  • 11
  • 2

1 Answers1

0

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
Barmar
  • 741,623
  • 53
  • 500
  • 612