Codewars SQL

SQL Intermediate: Add index column to a query

Return a new table from people table.

Your solution should have a row index number with the column name row number, id and the name of every people alphabetically.

This code can be used to normalize in REAL enviroments when you don’t have an id column and should generate it from scratch.

HINT: try to use ANSI SQL only. HINT: you can add generated indexes attaching to the first query, but is more elegant to retrieve them at the same query.

Tested with SQLite 3.2.8 but should work with MySQL, and SQL server.

Your solution generally will not work with PostgreSQL.

Solution:

select count(*) as 'row number', p.id, p.name
from people p, people x
where p.name >= x.name
group by p.id, p.name
order by 1;

Link