Current record/row ID in query

[RoryD]

How do I determine the current record id or row number returned by a query?
For example, if I have a query returning people who have paid and want to
number them in ascending order:

Table:
- Person, Char(50)
- Paid, Bool

Query should return:

ID Person
-- ---------
1 Jeff
2 Mike
3 Mary
etc.

I want to generate this ID on the fly - is there a RECORDID or
CurrentRecordNum value that shows the current record number returned by the
query?

 

[Michel Walsh]

Hi,


With Jet, no, unless you append the data to a table with an autonumber
field, and read the result from that table.


Hoping it may help,
Vanderghast, Access MVP

 

[RoryD]

Michel,

Thanks - I managed to work it out using an alias of [OL] for the table
[Overall Leaderboard]:

SELECT TOP 100 PERCENT
(SELECT COUNT(*)
FROM [Overall Leaderboard]
WHERE total > OL.total) + 1 AS Place,
Person, Total
FROM dbo.[Overall Leaderboard] OL
GROUP BY Person, Total
ORDER BY Total DESC

 

[Michel Walsh]

Hi,


Sure, ranking the records, as you do is a solution, but note that the query
runs at least twice, and potentially, N^2 times. For a large number of
records, creating a temporary table is probably much less expensive, but
since you use MS SQL Server 2000 ( I assume, with you dbo prefix ) you can
also use:

SELECT IDENTITY(int, 1, 1) as rank, au_id INTO #temp FROM authors
SELECT * FROM #temp

and the query (or here, the table authors) is touched just once.

With MS SQL Server 2005 Express (now in beta, freely available to the
public), you can use

ROW_NUMBER() OVER ( ORDER BY... )

as in:


SELECT ROW_NUMBER() OVER( ORDER BY Total ) , *
FROM [Overall Leaderboard]


Note that it is documented that the ROW_NUMBER() produces the result AFTER
the WHERE clause got evaluated, so it cannot appear, directly, in the WHERE
clause itself (unless indirectly, through the use of a sub-query, as a
virtual table, indeed).



Hoping it may help,
Vanderghast, Access MVP

Michel,

Thanks - I managed to work it out using an alias of [OL] for the table
[Overall Leaderboard]:

SELECT TOP 100 PERCENT
(SELECT COUNT(*)
FROM [Overall Leaderboard]
WHERE total > OL.total) + 1 AS Place,
Person, Total
FROM dbo.[Overall Leaderboard] OL
GROUP BY Person, Total
ORDER BY Total DESC

--
Regards,

RoryD

Hi,


With Jet, no, unless you append the data to a table with an autonumber
field, and read the result from that table.


Hoping it may help,
Vanderghast, Access MVP