Re: How to select specific rows in ANSI SQL ?

From: Heinz Huber <Heinz.Huber_at_elbanet.co.at>
Date: Thu, 28 Dec 2000 08:09:28 +0100
Message-ID: <3A4AE728.2A42545E_at_elbanet.co.at>

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.

There is a possibility as long as your order by includes the primary key of the most detailed table.
SELECT *
 FROM Table o
 WHERE Conditions AND

       (SELECT COUNT(*)
        FROM Table i
        WHERE Conditions AND i.PKey <= o.PKey) BETWEEN :n1 AND :n2

 ORDER BY PKey

If you have a more complex order by, the condition of the select count(*) gets pretty complicated:
ORDER BY Col1, Col2, PKey =>
(i.Col1 < o.Col1 OR (i.Col1 = o.Col1 AND (i.Col2 < o.Col2 OR (i.Col2 = o.Col2 AND i.PKey <= o.PKey))))

You can get around this, if you have another table that can be joined to the rows of the result set and contains a primary key which is in the order given by the order by cols. Then you can join with this table and order by its primary key.

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

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 - 08:09:28 CET