Re: Call for 7.5 feature completion

On Mon, 29 Aug 2005, Christopher Kings-Lynne wrote:

> Oh, and 'select rowid, * from table' which returns special rowid column
> that just incrementally numbers each row.

In sql2003 there is a window function called ROW_NUMBER() that can be used
to get numbers like that (one also need to specify the window to be the
full table in this case).

I think it can look like this (based on me reading the standard, i've not
tested it in one of the other databases that support window functions):

SELECT ROW_NUMBER() OVER (ORDER BY id), * FROM table;

The over part specify that the whole result set is the window and that the
row numbers should be assigned to the result in that order. In practice
you want that order to be the same as the whole order I guess

SELECT ROW_NUMBER() OVER (ORDER BY id), * FROM table ORDER BY id;

Based on some googeling DB2 seems to allow OVER () while oracle does not
and you need to specify the ORDER BY (or some other window definition) in
the OVER part.

Anyway, I just want to point out that row numbers are possible to get in
sql2003, even if a simpler syntax like the above can also be useful. Maybe
one can just extend sql2003 and let the OVER part be optional all
together, and use SELECT ROW_NUMBER(), * FROM table;

ps.

A window is similar to group by, but you keep all rows in the result set.
With group by you get one row from each group in the result set,

--
/Dennis Björklund