how can I covert row_number () over (partition by)  sql server function to to ms access?

hatic
hatic used Ask the Experts™
on
I'm trying to convert sql server code to access. basicly it groups by selected fields, and indexes every row within each group
like (partitioned by colm 1 and 2)
index/ colm1/ colm2/
1       / a       / x
2       /a        /x
1       /b        /y
1       /b        /w
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
your question is not clear
can you please elaborate?
Hi Hatic,

There is no equivalent function in MS Access.  (I'm trying to understand why you'd want to move from a real RDBMS to Access, but that's your busines....)

However, you can mimic the funcitonality.  It takes a unique column in the table and a bit of creative SQL.

Try the query below and see if it will work for you.


Good Luck,
Kent

SELECT t1.userid, t1.username, 
  (SELECT COUNT(*) FROM users AS t2 WHERE t2.userid <= t1.userid) AS RowNum 
FROM users t1 
ORDER BY t1.userid

Open in new window

Author

Commented:
I'll try with an explanation.
I have a book list. including same books with different editions. so name, author columns are same but edition column is different an I have lots of different books.
I want to index them within the same book. if a book has 3 editions first edition will be indexed 1, second ed will be 2 and third ed will have 3 in index column
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Kdo's solution should work,
but it is very inefficient
unfortunatly, that is the only alternative
Hi hatic,

The query above will generate row numbers over the entire result set, not "partitioned" by anything.  The query below should do that.

And it inefficient.  But if you're dealing with a few hundred rows and it's the most "practical" way to get there, that's what you're forced to do....


Kent

SELECT t1.userid, t1.username, 
  (SELECT COUNT(*) FROM users AS t2 WHERE t2.userid <= t1.userid AND t1.username = t2.username) AS RowNum 
FROM users t1 
ORDER BY t1.userid

Open in new window

Author

Commented:
Thanks Kdo, I've already did it, I took your first code and added the t1.username = t2.username part and it worked.
thanks a lot

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial