Sequential numbering of query results

Question

I’m not really close to the database, and actually I don’t even need such functionality, but just out of curiosity, it’s possible to create in Runtime, that is, during SELECT, a sequential numbering to be used in substitution for the auto-incremental values of a primary key, without changing any value, only facilitating the work of the language server-side?

Consider that Sqlfiddle for example.

In it there are three tables, and the third only relates the other two. I know this kind of intermediate table has a proper name, but I don’t remember >.<

The way the simple query was mounted, when rendering an HTML from a resource of this query, if I show the column values sid would look visually strange, for example, a rowset with three records start your listing by number four.

With language server-side Simply get the current iteration index over the resource and use this value instead of the one in the column. Or even I could assemble an unordered list and renumber with CSS(3).

But it’s directly by query? It is possible?

Answer 1

Solution for Mysql:

Whereas the numbering may be volatile, and Mysql does not have a line counter by default, it follows a query that fills 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 can’t do the SET separate:

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

It’s not really a primary key reordering, but if it’s just line numbering, I think it’ll solve. In this other answer I applied the same concept to result pagination, with an example of how to change the initial value of the index according to the pages.

See applied to SQL Fiddle.

Solution for T-SQL:

In T-SQL is easier, already has function ready for this:

SELECT
   ROW_NUMBER() OVER (ORDER BY campo1),
   t.campo1,
   t.campo2
FROM
   tabela t

Answer 2

On the @Bacco line, solution for the Oracle

SELECT
   ROWNUM,
   t.campo1,
   t.campo2
FROM
   tabela t

Answer 3

Following the example above, I only 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

Answer 4

For oracle I resolved as follows:

with test as ( SELECT
field1, peasant 2

FROM table ORDER BY campo1 )

SELECT ROWNUM AS SEQ, test. * FROM test;