Ordinal record number

[Dan]

How can i find out what record number in the return set I am at?

Example select *,recnum() from foo I would like to see recnum() be 1
then 2,3,4,5 and so on for each record returned.

[Blair Adamache]

You can add a column to the table using a SEQUENCE or IDENTITY value.
Or, use the rownum function:

SELECT * FROM (SELECT NAME, rownumber() OVER
(ORDER BY NAME)
AS rn FROM ADDRESS)
AS tr
Dan wrote:

How can i find out what record number in the return set I am at?

Example select *,recnum() from foo I would like to see recnum() be 1
then 2,3,4,5 and so on for each record returned.

[Dave Hughes]

On 2004-04-13, Dan scribbled:

How can i find out what record number in the return set I am at?

Example select *,recnum() from foo I would like to see recnum() be 1
then 2,3,4,5 and so on for each record returned.

Hi Dan,

AFAIK, you can't directly. Records aren't "numbered" in a relational
database as they are fundamentally unordered. According to relational
theory which is more or less the basis for modern relational databases,
a table (or "relation" in relational theory) is a *set* of tuples where
the word "set" is used in its strict mathematical sense to mean "a
distinct, unordered set of values". Therefore a record can't have a
number.

It would be possible to implement this using a stored procedure which
incremented a counter and included it within the result set it
returned, but you can't implement a function like the RECNUM() function
you describe (I think?). However, I've usually found that what people
wishing to do this /actually/ want to do is /rank/ records. This can be
done using the OLAP functions RANK() or DENSE_RANK() in DB2 (at least
in v7 and upwards - not sure about prior versions).

For example, given the following table of data:

SALES
--------+-------+-------
COUNTRY | MONTH | AMOUNT
--------+-------+-------
GB | 1 | 50000
GB | 2 | 49000
GB | 3 | 53000
FR | 1 | 51000
FR | 2 | 49000
FR | 3 | 52000
DE | 1 | 48000
DE | 2 | 47000
DE | 3 | 51000
--------+-------+-------

You could use the following SQL to rank sales in month 1 by country:

SELECT
RANK() OVER (ORDER BY AMOUNT DESC) AS RANK,
COUNTRY,
AMOUNT
FROM SALES
WHERE MONTH = 1

Which returns the following result:

RANK COUNTRY AMOUNT
---- ------- ------
1 FR 51000
2 GB 50000
3 DE 48000

However, it should be noted that ranking is *not* the same as record
numbering. For example, in month 2 both GB and FR have the same value
in the AMOUNT field. What happens when the above query is executed for
month 2?

RANK COUNTRY AMOUNT
---- ------- ------
1 GB 49000
1 FR 49000
3 DE 47000

Note that the RANK() function assigns the same rank to GB and FR (which
is correct), and then skips a rank before assigning 3 to the DE row. If
you don't want it to skip a rank, use the DENSE_RANK() function instead
which would produce the same ranking for GB and FR, but would assign 2
to the DE row instead.

For more information on the OLAP functions in DB2, read the "Language
Elements" chapter in the SQL Reference. Under the "Expressions" section
you should find the "OLAP functions" section.

Anyway, sorry it's probably not the exact answer you're looking for but
hopefully its given you some ideas to play with.

HTH,
Dave.
--
Dave
Remove "_nospam" for valid e-mail address

"Never underestimate the bandwidth of a station wagon full of CDs doing
a ton down the highway" -- Anon.

[Dave Hughes]

On 2004-04-13, Blair Adamache scribbled:

You can add a column to the table using a SEQUENCE or IDENTITY value.
Or, use the rownum function:

SELECT * FROM (SELECT NAME, rownumber() OVER
(ORDER BY NAME)
AS rn FROM ADDRESS)
AS tr
Dan wrote:

How can i find out what record number in the return set I am at?

Example select *,recnum() from foo I would like to see recnum() be
1 then 2,3,4,5 and so on for each record returned.

Damn! How did I miss that? Time to go and RRTFM :-) Still, guess I was
right on one point - a result set requires an explicit order before it
can be numbered in any fashion.

Dave.

--
Dave
Remove "_nospam" for valid e-mail address

"Never underestimate the bandwidth of a station wagon full of CDs doing
a ton down the highway" -- Anon.

[Knut Stolze]

Dave Hughes wrote:

On 2004-04-13, Blair Adamache scribbled:

You can add a column to the table using a SEQUENCE or IDENTITY value.
Or, use the rownum function:

SELECT * FROM (SELECT NAME, rownumber() OVER
(ORDER BY NAME)
AS rn FROM ADDRESS)
AS tr

Damn! How did I miss that? Time to go and RRTFM :-) Still, guess I was
right on one point - a result set requires an explicit order before it
can be numbered in any fashion.

You can omit the ORDER BY:

SELECT name, rownumber() over() AS rn
FROM ...

Then you get any arbitrary order from DB2, which might even change from
execution to execution of the statement.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Dave Hughes]

On 2004-04-14, Knut Stolze scribbled:

Dave Hughes wrote:

On 2004-04-13, Blair Adamache scribbled:

You can add a column to the table using a SEQUENCE or IDENTITY value. >> Or, use the rownum function:
SELECT * FROM (SELECT NAME, rownumber() OVER
(ORDER BY NAME)
AS rn FROM ADDRESS)
AS tr

Damn! How did I miss that? Time to go and RRTFM :-) Still, guess I
was right on one point - a result set requires an explicit order
before it can be numbered in any fashion.

You can omit the ORDER BY:

SELECT name, rownumber() over() AS rn
FROM ...

Then you get any arbitrary order from DB2, which might even change
from execution to execution of the statement.

Argh! Just checked and you're absolutely right, Knut - apologies Dan!
Only RANK and DENSE_RANK require an ORDER BY in the OVER clause.

--
Dave
Remove "_nospam" for valid e-mail address

"Never underestimate the bandwidth of a station wagon full of CDs doing
a ton down the highway" -- Anon.