Re: [sqlite] Are my assumptions about the implyed OID valid?

[Eric Bohlman]

Michael Scharf wrote:

-- The user sorts on NUMBER and selects NAME that
-- start with 'foo'. I create a temp table view1.
-- I make the assumption that view1.oid is in order
-- (1...size_of_the_table)
-- This is currently true with sqlite 3.2.7

CREATE TEMP TABLE view1 AS
  SELECT oid AS dataid
  FROM data
  WHERE name like 'foo%' ORDER number;

-- when some columns are displayed in the UI I fetch
-- the items. Say row 100-150 is to be displayed:

  SELECT *
  FROM data
  WHERE oid IN
     (SELECT dataid
      FROM view1
      WHERE rowid between 100 AND 150)
  ORDER BY number; -- this sorting is needed, to
                   -- get the result in order!

This work fine currently. The question is, is this an artifact
of the current implementation or can I rely on this in the
future? (I know that's not compatible with the SQL specs)

Wouldn't LIMIT and OFFSET do the trick for you?