So i'm used to using mysql, but i'm on a project right now that uses an access database, and I'm looking for a way to replace LIMIT. I googled around and found the information on TOP, but it doesnt totally replace the functionality. For example if a user were to search the database for phone numbers of anyone named jim... and wanted to page through 50 at a time it would look something like this in mysql. SELECT phonenumber FROM users WHERE firstname= 'jim' LIMIT 0, 50; That would return the first 50, page 2 would look like: SELECT phonenumber FROM users WHERE firstname= 'jim' LIMIT 49, 50; page 3 SELECT phonenumber FROM users WHERE firstname= 'jim' LIMIT 99, 50; Using the TOP clause I would do something like SELECT TOP 50 phonenumber FROM users WHERE firstname= 'jim' That would only get me my first page.... how do I tell it to skip a certain number of records, then grab 50.
headedtomexico 0 Junior Poster in Training
headedtomexico 0 Junior Poster in Training
OK, i found the answer, I guess I was being hasty when i did it, here it is if anyone else stumbles onto this post needing help
Page1 SELECT TOP 50 phonenumber FROM users WHERE firstname = 'jim'; Page2 SELECT TOP 50 phonenumber FROM users WHERE firstname= 'jim' AND phonenumber NOT IN (SELECT TOP 50 phonenumber FROM users WHERE firstname = 'jim') ;
Page3 SELECT TOP 50 phonenumber FROM users WHERE firstname= 'jim' AND phonenumber NOT IN (SELECT TOP 100 phonenumber FROM users WHERE firstname = 'jim');
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.