Re: How to select specific rows in ANSI SQL ?

Thought about another simplification (see below).

Heinz Huber wrote:
>
> bdimple_at_my-deja.com wrote:
> >
> > Is it possible to code the equivalent of MySQL Specific LIMIT
> > using ANSI SQL?
> >
> > For example, select * from where... LIMIT n1, n2?
> > gives you the records starting from the n1-th result row and n2 results?
> >
> > I have tried joining the table to itself but I can't get it to work.
> >
> > I'd be very grateful for any and all suggestions.

[snipped other possibilities]

> If the OrderTable is filled in the wanted order and only with the rows
> that should be in the resultset, the query would be like this:
> INSERT INTO OrderTable
> SELECT PKey, NUMBER(*)
> FROM Table
> WHERE Conditions
> ORDER BY Col1, Col2, PKey;
> The function NUMBER(*) is NOT standard SQL! You'll have to figure out a
> way to do this yourself for every DBMS. Some DMBS offer a datatype
> IDENTITY or simular which is automatically filled in ascending order.
>
> SELECT t.*
> FROM Table t, OrderTable o
> WHERE t.PKey = o.PKey AND
> (SELECT COUNT(*)
> FROM OrderTable i
> WHERE i.Ordering <= o.Ordering) BETWEEN :n1 AND :n2
> ORDER BY o.Ordering

If you can guarantee numbering from 1 in steps of 1, you can even omit the subselect and just use the column Ordering: SELECT t.*
 FROM Table t, OrderTable o
 WHERE t.PKey = o.PKey AND

       o.Ordering BETWEEN :n1 AND :n2
 ORDER BY o.Ordering

This WON'T work with the following though:

> Or if you prefer the conditions in the second query:
> INSERT INTO OrderTable
> SELECT PKey, NUMBER(*)
> FROM Table
> ORDER BY Col1, Col2, PKey;
>
> SELECT t.*
> FROM Table t, OrderTable o
> WHERE Conditions AND t.PKey = o.PKey AND
> (SELECT COUNT(*)
> FROM Table t, OrderTable i
> WHERE Conditions AND i.Ordering <= o.Ordering) BETWEEN :n1 AND
> :n2
> ORDER BY o.Ordering

hth,
Heinz Received on Thu Dec 28 2000 - 09:57:06 CET