|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
February 2nd, 2005, 07:35 PM
|
|||
|
|||
|
how to filter 5 records at a time!??
i was wondering if it was possible to get 5 records at a time from a firebird database!??
(using delphi).... thank you...
|
|
#3
February 3rd, 2005, 08:21 AM
|
|||
|
|||
|
Something like:
SELECT FIRST 4 SKIP 5 * FROM MYTABLE
From documentation:
(1.0) SELECT [FIRST (<integer expr m>)] [SKIP (<integer expr n>)]
(1.5) SELECT FIRST can now take zero as argument
FB 1.5 allows zero as an argument of FIRST. An empty result set will be returned.
Retrieves the first m rows of the selected output set. The optional SKIP clause will cause the first n
rows to be discarded and return an output set of m rows starting at n + 1. In the simplest form, m
and n are integers but any Firebird expression that evaluates to an integer is valid. A identifier that
evaluates to an integer may also be used in GDML, although not in SQL or DSQL.
Parentheses are required for expression arguments and optional otherwise.
They can also bind variables, e.g. SKIP ? * FROM ATABLE returns the remaining dataset after discarding
the n rows at the top, where n is passed in the "?" variable. SELECT FIRST ? COLUMNA, COLUMNB FROM
ATABLE returns the first m rows and discards the rest.
The FIRST clause is also optional, i.e. you can include SKIP in a statement without FIRST to get an
output set that simply excludes the rows appointed to SKIP.
Available in SQL and DSQL except where otherwise indicated.
Examples:
SELECT SKIP (5+3*5) * FROM MYTABLE;
SELECT FIRST (4-2) SKIP ? * FROM MYTABLE;
SELECT FIRST 5 DISTINCT FIELD FROM MYTABLE;
Two Gotchas with SELECT FIRST
1. This
delete from TAB1 where PK1 in (select first 10 PK1 from TAB1);
will delete all of the rows in the table. Ouch! the sub-select is evaluating each 10 candidate rows for
deletion, deleting them, slipping forward 10 more...ad infinitum, until there are no rows left.
Beware!
2. Queries like:
...
WHERE F1 IN ( SELECT FIRST 5 F2 FROM TABLE2 ORDER BY 1 DESC )
won't work as expected, because the optimization performed by the engine transforms correlated
WHERE...IN (SELECT...) predicates to a correlated EXISTS predicate. It's obvious that in this case FIRST
N doesn't make any sense:
WHERE EXISTS (
SELECT FIRST 5 TABLE2.F2 FROM TABLE2
WHERE TABLE2.F2 = TABLE1.F1 ORDER BY 1 DESC )
Note that FB2 supports ROWS (like interbase 7.x)
FirebirdSQL Foundation member.
- Join today at http://www.firebirdsql.org/ff/foundation JEDI VCS contributor http://jedivcs.sourceforge.net/
|
|
| Viewing: Dev Shed Forums > Databases > Firebird SQL Development > how to filter 5 records at a time!?? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
