There is a table in which identifiers do not go in order:

CREATE TABLE random(ID INT primary key , Value INT unique ) INSERT INTO random values (1,1), (2,3), (10,7), (4,10), (5,16), (6,17), (17,33), (8,21), (67,27), (11,41), (99,25), (12,13), (13,23), (14,29), (1000,9)

It is necessary to create an additional row numbering column without using the row_number() function

My attempts:

Generated numbering like this:

 select id, value, count(*) over (partition by 1 order by id ) as rnum from random

but this request sorts by key, and the original order is needed.

Is it possible to somehow glue the rnum column to the right "to the original query "select * from random" ?

Updated table can not be changed; "splicing" is needed in the query, or another query in which, for example, you can refer to line No. 7

  • 2
    And what is the "original order" SQL does not guarantee the order of records in the table at all, unless a specific order by is specified. - Mike
  • Should the column physically appear in the table or do you need to achieve the output of an additional value by a SELECT query? - cheops
  • If you need the “original order”, add an auto-increment column to the table. - msi
  • the column must participate in the query; physically it is not needed. The original order means the one that select * from random, you cannot change the table, you need a query - ketchyn
  • can try rank() - Saidolim

1 answer 1

And why not ROW_NUMBER ()? Here you have two options with ROW_NUMBER () and c COUNT (*)

 SELECT ROW_NUMBER()OVER()rownum, COUNT(*)OVER(ROWS UNBOUNDED PRECEDING)countnum, * FROM (values (1,1), (2,3), (10,7), (4,10), (5,16), (6,17), (17,33), (8,21), (67,27), (11,41), (99,25), (12,13), (13,23), (14,29), (1000,9))T(ID, Value) rownum countnum id value 1 1 1 1 2 2 2 3 3 3 10 7 4 4 4 10 5 5 5 16 6 6 6 17 7 7 17 33 8 8 8 21 9 9 67 27 10 10 11 41 11 11 99 25 12 12 12 13 13 13 13 23 14 14 14 29 15 15 1000 9

But keep in mind that the order of the output rows without sorting in the database is not guaranteed.

In particular, the optimizer can insert its own sorting, for example for JOIN or UNION operators, and then you will get the wrong result.

In general, when you use numbering without sorting — on the same data, the same query can give different results!

(this may depend on the server version; current table statistics - which affect the query plan)