B V, Phanisekhar <[EMAIL PROTECTED]> wrote:
Why Sqlite doesn't support just the use of offset in select
statements? As such SQL does support the use of offset only, without
limit. But in case of Sqlite it's not possible to use offset without
limit.

select * from tableName limit -1 offset 5;
-- or
select * from tableName limit 5, -1

Can we have the following statement?

Select * from tableName offset 3

No.

I believe the above query is supported by SQL; but it's not supported
by
Sqlite.

As far as I can tell, LIMIT and OFFSET clauses are not specified in any version of SQL standard. What is your belief based on that this query is supported by SQL, and what precisely do you mean by the term "SQL" in this assertion?

An expression is allowed. I use a query that has a subselect in the
LIMIT clause (I need to return, say, top 10% of all the records).

Can we have subselect in Limit Clause?

Yes. Like I said, I use it, and it works. I have something along the lines of

select * from tableName
order by someField DESC
limit ROUND( (select count(*) from tableName) / 10);

to retrieve top 10% of the records.

On the website it says:
"The limit is applied to the entire query not to the individual SELECT
statement to which it is attached."

What does the above statement mean?

It means that you can't have a query like this:

select * from table1 limit 10
union all
select * from table2 limit 20;

You can have a single LIMIT clause at the end that applies to the union as a whole.

How will it work for the below
given
query?

Select * from mainTable where xxx in (select yyy from table1) limit 10

It will work just fine.

Will the limit work for the subselect query also?

The limit as written does not apply to the subselect. But you can give it its own limit:

Select * from mainTable where xxx in (select yyy from table1 limit 20) limit 10;

Not that such a query makes much sense.

Igor Tandetnik

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------

Reply via email to