Access & ROW_NUMBER function

[TomasGreif]

Hi,
I can't find function, which is similar to ROW_NUMBER function in Oracle. This function is desribed below.
Any idea will be helpful.
Thanks.

ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause,
beginning with 1. By nesting a subquery using ROW_NUMBER inside a query that retrieves the ROW_NUMBER values for a specified range, you can find a precise subset of rows from the results of the inner query. This use of the function lets you implement top-N, bottom-N, and inner-N reporting. For consistent results, the query must ensure a deterministic sort order. You cannot use ROW_NUMBER or any other analytic function for expr. That is, you cannot nest analytic functions, but you can use other built-in function expressions for expr. Examples

For each department in the sample table oe.employees, the following example assigns numbers to each row in order of employee's hire date:
SELECT department_id, last_name, employee_id, ROW_NUMBER()
OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id
FROM employees;
DEPARTMENT_ID LAST_NAME EMPLOYEE_ID EMP_ID
------------- ------------------------- ----------- ----------
10 Whalen 200 1
20 Hartstein 201 1
20 Fay 202 2
30 Raphaely 114 1
30 Khoo 115 2
30 Baida 116 3
30 Tobias 117 4
30 Himuro 118 5
30 Colmenares 119 6
40 Mavris 203 1
. . .
100 Popp 113 6
110 Higgins 205 1
110 Gietz 206 2
ROW_NUMBER is a nondeterministic function. However, employee_id is a unique key, so the results of this application of the function are deterministic.

 

[Tranman]

ROWNUM does not exist in Access. I've often wished it did.

If you're wanting to use it to select the first n rows, in Jet SQL, you can say, "Select Top n ID, NAME, <whatever> from CUST order by ID"
...something like that.

Of course, if you want the bottom n, just sort desc.

Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[TomasGreif]

Thanks for your response...What I want is not first n rows but something like this:

SELECT
CustomerID, ContactDate, ROW_NUMBER()OVER (PARTITION BY CustomerID ORDER BY ContactDate) AS ContactNo
FROM CustomersContact;

It's ranking within a subgroups of rows...

I stil think it should be somehow possible to done this in Access.

Tomas

 

[PHV]

Something like this ?
SELECT A.CustomerID, A.ContactDate
, (SELECT Count(*) FROM CustomersContact B WHERE B.ContactDate<=A.ContactDate) AS ContactNo
FROM CustomersContact AS A

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886