Re: pager

[prev] [thread] [next] [Date index for 2004/10/22]

From: David R. Baird
Subject: Re: pager
Date: 12:52 on 22 Oct 2004

Yes, it seems like there are a couple of fairly intricate but widely 
supported ways of munging the SQL to emulate LIMIT. I'd need to be 
able to get my hands on the entire SQL statement though, I'll have a 
rummage around and see where I can get it. 

Cheers,

d.

On 22 Oct 2004 at 14:21, Emanuele Zeppieri wrote:

> From: David R. Baird [mailto:dave@xxxxxxxxxxxxx.xx.xx] 
> Sent: Friday, October 22, 2004 11:20 AM
> To: cdbi-talk@xxxxxx.xxxxx.xxx
> Subject: Re: pager
> 
> > OK, that would be good, but now I need to know which databases use 
> > which syntax. Any info gratefully received. But I suppose these can 
> > be added over time. At the moment all I know is Postgre, MySQL, and 
> > InterBase/FireBase. I suspect various other databases use the same 
> > syntax as one of these. And I think both MySQL and Postgre changed 
> > their LIMIT syntax in fairly recent versions, I'm sure I can track 
> > that down. 
> 
> Cross DB servers LIMIT/OFFSET support can really be very intricate.
> Some DBMSs use a completely different sintax for the LIMIT clause and
> don't offer any kind of OFFSET clause (most noticeable MS Access and MS
> SQL Server,) therefore you have to simulate it in some way, usually
> wrapping your original query with extra SELECTs.
> In MS Access for example, to add LIMIT/OFFSET to this query:
> 
> SELECT my_column
> FROM my_table
> ORDER BY my_column ASC
> 
> say with the values LIMIT=5 OFFSET=10, you have to resort to the TOP
> clause and re-write it this way:
> 
> SELECT * FROM (
> 	SELECT TOP 5 * FROM (
> 		SELECT TOP 15 my_column
> 		FROM my_table
> 		ORDER BY my_column ASC
> 	) AS foo ORDER BY my_column DESC
> ) AS bar ORDER BY my_column ASC
> 
> (the innermost TOP clause takes the value 15=5+10.)
> To sum up, be ware that you won't find just slight sintactic differences
> like that between MySQL and Postgres, if you really want to support any
> DB server.
> 
> Cheers,
> Emanuele Zeppieri.




        -- 
        Dr. David R. Baird
Riverside Content Management Systems
http://www.riverside-cms.co.uk