MS Access equivalent of ROW_NUMBER() and OVER()

Question

I've the following sql statement running in sql server-

SELECT Id,Name
       FROM
       (
          SELECT ROW_NUMBER() OVER( ORDER BY Id DESC ) AS row, Id, Name from BRS 
       )
AS data WHERE row BETWEEN 1 AND 5 ORDER BY Id DESC;

Now, I would like to implement same technique in MS Access database. But it gives syntax error for ROW_NUMBER() and OVER()

Is there any MS Access 2007 syntax of ROW_NUMBER() and OVER()?

Have a look at Gordon Linoff's answer [here](http://stackoverflow.com/questions/18753368/row-number-in-access-select-statement). — Tim Biegeleisen, Jan 04 '16 at 05:46

score 0 · Answer 1 · answered Jan 04 '16 at 09:38

0

You can do like this:

SELECT TOP 5 
    (Select Count(*) From BRS As B Where B.Id >= BRS.Id) As Row,
    BRS.Id, 
    BRS.Name
FROM 
    BRS
ORDER BY 
    BRS.Id DESC;

Result:

Id  Name    Row
10  Test    1
9   TestTwo 2
8   Thing   3
6   Another 4
5   Anyone  5

answered Jan 04 '16 at 09:38

Gustav

53,498
7
29
55

Thanks. What if I want to get next 5 records? – s.k.paul Jan 04 '16 at 10:28
One method is to _select top 10_, then select from these those _not in select top 5_. – Gustav Jan 04 '16 at 11:09

MS Access equivalent of ROW_NUMBER() and OVER()

1 Answers1