Sequential numbering of query results
I'm not very close to database, and in fact I don't even need such functionality, but just out of curiosity, is it possible to create in runtime, that is, during SELECT, a sequential numbering to be used in place of the auto-incremental values of a primary key, without changing any value, just facilitating the work of the language server-side?
Consider that SQLFiddle of example.
In it there are three tables, the third of which only relates the other two. I know that this type of intermediate table has a proper name, but I don't remember >.
The way the simple query was assembled, when rendering an HTML from a resource of that query, if I show the column values sid it would look visually strange, for example, a RowSet with three records start its listing by the number four.
With a language server-side it would be enough to get the current index of the iteration on the resource and use this value instead of the present one in the column. Or still I could assemble an unordered list and renumber with CSS(3).
But and directly by the query? Is it possible?
3 answers
Solution for MySQL:
Considering that numbering can be volatile, and that MySQL does not have a row counter by default, follows a query that supplies the count using @variáveis:
SET @contador := 0;
SELECT
@contador := @contador + 1 AS linha,
t.campo1,
t.campo2
FROM
tabela t
If for some reason you cannot do the separate SET:
SELECT
@contador := @contador + 1 AS linha,
t.campo1,
t.campo2
FROM
(SELECT @contador := 0) AS nada,
tabela t
And if you want to number the result of a complex query
SELECT
@contador := @contador + 1 AS linha,
t.campo1,
t.campo2
FROM
(SELECT @contador := 0) AS nada,
(SELECT SUM(campo) FROM tabela GROUP BY algumacoisa JOIN outracoisa ... ) AS t
Doesn't amount to a primary key reorder, but if it's just for row numbering, I think it solves it. in this other response I applied the same concept to result pagination, with an example of how to change the initial value of the index as per the pages.
See applied to SQL Fiddle.
solution for T-SQL:
In T-SQL it becomes easier, it already has a function ready for this:
SELECT
ROW_NUMBER() OVER (ORDER BY campo1),
t.campo1,
t.campo2
FROM
tabela t
In @Bacco row, solution for Oracle
SELECT
ROWNUM,
t.campo1,
t.campo2
FROM
tabela t
Following the example above, I just did a treatment to display the first one that diverges from the id in the table.
SET @contador := 0;
select linha,id from (SELECT
@contador := @contador + 1 AS linha,
t.id
FROM
`TABLEX` t
order by id) ttt where linha <> id