Sequential numbering of the results of a query

Question

Sequential numbering of the results of a query

Navigation

#1 by (9 votes)
#2 by (6 votes)

1

I'm not very close to the database, and in fact I do not even need such functionality, but just for the sake of curiosity, it's possible to create in runtime , that is, during SELECT , a sequential numbering to be used in substitution of the auto-incremental values of a primary key, without changing any value, only facilitating the work of the server-side language?

Consider this example SQLFiddle .

There are three tables in it, and the third only lists the other two tables. I know this type of intermediate table has a proper name, but I do not remember>.

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

With the server-side language it would be enough to get the current index of the iteration over the resource and use that value instead of the one in the column. Or I could put together an unordered list and renumber it with CSS (3).

But is it directly from the query ? Is it possible?

sql query

asked by anonymous 13.12.2014 / 18:07

2 answers

6

In the line of @Bacco, solution for Oracle

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

13.12.2014 / 21:44

How to change the date field in the Materialize How to convert the fields to a row with string?

score 9 · Accepted Answer

Solution for MySQL:

Considering that the numbering may be volatile, and that MySQL does not have a line counter by default, it 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 can not do SET separated:

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 a primary key reordering, but if it's just for line numbering, I think it works out. In this other answer I applied the same concept for paging results, with an example of how make the initial index value change according to the pages.

See applied to SQL Fiddle .

Solution for T-SQL:

In T-SQL it's easier, you already have a function ready for it:

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