Guriddo » Topic: LIMIT function mysql/access/mssql

LIMIT function mysql/access/mssql

Guriddo
LIMIT function mysql/access/mssql

This topic has 3 replies, 2 voices, and was last updated 16 years, 8 months ago by tony.

Creator

Topic
October 26, 2009 at 6:28 pm #74458

admin
Keymaster

All the demo's use MySQL and that Bloody nice LIMIT function for doing the paging of data. We are trying to implement the large data (12000 records) example in ASP using ACCESS & MSSQL (we got it all working with mySQL but project has to be MSSQL/ACCESS only)

They do not have the equivalent LIMIT functionality, (No! TOP WILL NOT WORK after reading 1000 forum posts). As Jqgrid is independant of what ever server-side DB/Language, why is it so reliant on the limit function of mysql how do we do a large database without/with pagination but are able to do the auto search function like in bigset.html.

Regards

MArk
Creator

Topic

Viewing 3 replies - 1 through 3 (of 3 total)

Author

Replies
October 27, 2009 at 4:47 am #90051

tony
Keymaster

Hello,

I will just give you the point – you will do the rest:

So let suppose that we want to emulate the following mysql sql

select * from mytable limit 10, 20

In MSSQL this can be done like this:

select top 20 *
from
(select top 30 *
from mytable
order by sortname)
order by sortname sortorder

If you use MSSQL 2005 you can read more here

http://www.select-sql.com/mssql/how-to-make-limit-from-to-in-mssql-2005.html

Regards

Tony

October 27, 2009 at 6:11 am #90058

admin
Keymaster

Hi Tony, thanks for reply.

I have seen both code examples before and neither work properly (access), mssql link has it's limits.

select * from mytable limit 10, 20

Start from record 11 and give us the next 20 records (11-30)

In Access and MSSQL your example will not start from a given record point, for example if we have 12000 records like the bigset examples, then doing:

select top 20 *
from
(select top 30 *
from tblInvoice
order by invid)
order by invid asc

will always give us records 1-20

October 27, 2009 at 7:17 am #90061

tony
Keymaster

Hello,

Sorry – you are right. I do not have work long time with MSSQL, so this was first that I have in mind.

Googling it I found this:
Author

Replies

Viewing 3 replies - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.

Stay connected with us in your favorite flavor!

Copyright 2014 TriRand LtdAll Rights ReservedRSS