0

I have some SQL table, say with a single column c1:

c1
10
3
1
10
5

Now, I'd like to issue an SQL command (not some operation of my DBMS, which I have intentionally not mentioned) which causes my table to be:

c1 record_index
10 0
3 1
1 2
10 3
5 4

Very simple... can this be done? Obviously, you don't know in advance the length of the table, so no SQL insertion or similar tricks.

Note: I'd like a general answer, but to be specific - I'm working with MonetDB.

Toastrackenigma
  • 7,604
  • 4
  • 45
  • 55
einpoklum
  • 118,144
  • 57
  • 340
  • 684

2 Answers2

2

There is no such thing as a "row index" in a relational database (note that a relation database contains rows, not "records"), but if you can find a column by which to sort the result this can easily be done using a window function:

select c1,
       row_number() over (order by some_column) as record_index
from the_table
order by record_index;

(You didn't specify your DBMS, the above is ANSI SQL)

Edit

if you don't have a column to sort on, you can try sorting by a constant value instead which will bring back the rows "unordered":

select record_index,
       row_number() over (order by 42) as record_index
from the_table
order by record_index;

But again: there is no such thing as a "natural" order of rows. There might be one on disk, but that is most likely not the one that is being used during retrieval - especially when the table doesn't have a clustered index (Oracle does not use a clustered index by default, Postgres doesn't have them at all).

A DBMS might also apply additional optimizations when retrieving the data. Oracle, Postgres and I believe SQL Server as well can e.g. "hop" on a table scan from a different session to optimize the phyiscal read from disk. In that case two concurrent selects will show a different "order" even though the phyiscal layout on the harddisk didn't change.

And then you have changes to the physical storage due to updates (e.g. if a row doesn't fit on the block any longer because its size increased).

einpoklum
  • 118,144
  • 57
  • 340
  • 684
  • 2
    The column to sort by appears to be the OP's biggest challenge. – Mike Sherrill 'Cat Recall' Feb 18 '14 at 12:33
  • @MikeSherrill'Catcall': yes, absolutely. But without more information about the *actual* problem, it's hard to tell. –  Feb 18 '14 at 12:35
  • Well, there are no other columns. I know the order is not intrinsic to the relation - but, an RDBMS eventually has to order its output _somehow_, and I want to catch _that_ order. – einpoklum Feb 18 '14 at 13:15
  • 1
    @einpoklum: there is no "that order". Modern DBMS apply several optimizations when retrieving data, so the order you see *now* is not necessarily the order you see with the next statement e.g. due to synchronized table scans - and I'm not even talking about physical changes to the storage due to DML. You can however try to use `order by null` in the window definition. –  Feb 18 '14 at 13:17
  • I can't `order by 42` in MonetDB... nor can I `order by null`... is that standard SQL? – einpoklum Feb 26 '14 at 16:48
0

Inspired by @a_horse_with_no_name 's answer...:

SELECT 
  c1, 
  row_number() OVER (ORDER BY dummy)-1 AS record_index
FROM 
  (SELECT 
    c1,
    42 AS dummy 
  FROM t1
) AS t1_augmented;

(Noting of course that row_number()'s result is 1-based.)

Community
  • 1
  • 1
einpoklum
  • 118,144
  • 57
  • 340
  • 684