B V, Phanisekhar wrote:
Can we have the following statement?
Select * from tableName offset 3
I believe the above query is supported by SQL; but it's not supported by
Sqlite.
The limit and offset clauses are not part of the SQL standard. They are
commonly implemented extensions that vary from one implementation to
another. SQLite has adopted the mySQL syntax:
[LIMIT {[/|offset|/,] /|row_count|/ | /|row_count|/ OFFSET /|offset|/}]
whereas you seem to be describing the PostgreSQL syntax:
[ LIMIT { /number/ | ALL } ] [ OFFSET /number/ ]
This omission has been noted in the SQL:2003 standard as a "Language
Opportunity" with the following comments:
The following Language Opportunity has been noted:
Severity: Language Opportunity
Reference: P02, SQL/Foundation, No particular location
Note at: None.
Source: Email from Troels Arvis, 2003-07-22
Language Opportunity:
Please consider adding a standardized way to limit the size of a result
set. The majority of SQL
DBMSs seem to already to that to certain extends, but with different syntax.
I think it’s a shame that such a useful feature isn’t standardized: It’s
more basic and simple
than objects, XML, etc. But still an issue which deserves some
attention, I believe.
Limiting what parts of a result set is returned is - of course - only
useful if the result set is
ordered. If it’s ordered, it’s very practical to be able to ask that
that - e.g. - only a maximum
of X rows are returned, perhaps after having skipped Y rows in the
result set. I often use it in
paginated data listings where it’s useless to work with the complete
result set.
In PostgreSQL, you can do:
select * from country order by id_numeric limit 5 offset 5;
SELECT id_numeric,iso_name
FROM country
ORDER BY id_numeric
LIMIT 30 OFFSET 60;
This way, I’ll get a maximum of 30 result set rows, after the system has
skipped the first 60
rows.
Microsoft SQL Server has a somewhat similar approach:
SELECT TOP 30 id_numeric, iso_name
FROM country
ORDER BY id_numeric;
- but here you cannot specify an offset which makes the feature less useful.
There are several other implementations. Around the Web, you may see a
large number of workarounds,
stored procedures and other over-complex machinery to try to handle the
different
implementations (or emulate the operations when the products don’t have
the feature).
I believe that something along the line of PostgreSQL’s syntax is
readable and compact.
Proposed Solution:
None provided with comment.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
