jQuery Grid Plugin - jqGrid - Topic: LIMIT function mysql/access/mssql

tony on LIMIT function mysql/access/mssql

Tue, 27 Oct 2009 07:17:33 +0200

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: http://www.planet-source-code......8;lngWId=5

SELECT TOP [x] [fields]
FROM [table]
WHERE ([clause])
AND [pKey] NOT IN (

SELECT [pKey] FROM (

SELECT TOP [y] [fields2]
FROM [table]
WHERE [clause]
ORDER BY [sort]) as [Tbl1])

ORDER BY [sort]

[x] = num rows
[y] = 0 based offset
[fields] = fields you want
[table] = table / joins / whatever
[clause] = where clause
[pKey] = primary key of table / virtual view in [table]
[fields2] = Only requires [pKey] and [sort] columns
[sort] = normal sort. Identical for both incl. ASC / DESC
[Tbl1] = name doesn't matter, it's for parser, not for you.

Hope this will help.

Regards

Tony

Mark C on LIMIT function mysql/access/mssql

Tue, 27 Oct 2009 06:11:45 +0200

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 as the TOP 30 is just reducing the 12000 records to the top 30 right?

Or our we missing something obvious? sortorder? different sortfield? remembering we want to use 12000 records and paginate them like the examples.

Will always give you records 1-20 when really your after records

At the moment our database will probably star with 1000 records but future grow to 10000 so we might see how long it takes to just do a once load all records, as we really need scrolling loading no paging with the auto increment search facility like in the examples.

We'll have a play with the demo's.

Regards

Mark C.

tony on LIMIT function mysql/access/mssql

Tue, 27 Oct 2009 04:47:14 +0200

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/mssq.....-2005.html

Regards

Tony

Mark C on LIMIT function mysql/access/mssql

Mon, 26 Oct 2009 18:28:57 +0200

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