Re: [firebird-support] How do I limit an SQL search list to 10 records?
Good clarification. Any downside to using an auto increment number? ``` fiKeyID INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ``` On Fri, Dec 13, 2019 at 12:15 PM Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: > > > On 13/12/2019 19:07, Clyde Eisenbeis cte...@gmail.com [firebird-support] > wrote: > > Is a field I create, or is it part of all records? > > It is a field you create. Which is why I wrote "Where is the > table in question, and is a suitable column (eg a timestamp) > to determine what the newest record is." > > You might be able to use the pseudo-column RDB$RECORD_VERSION, but that > is not necessarily in the right order of recency, because the value is > the transaction-number that wrote the last version of the record. This > means that updates to a record will also make it 'recent' and a record > written by a long-running transaction might not show up as 'recent' (or > at least as older than it may actually be). > > Mark > -- > Mark Rotteveel > >
Re: [firebird-support] How do I limit an SQL search list to 10 records?
On 13/12/2019 19:07, Clyde Eisenbeis cte...@gmail.com [firebird-support] wrote: > Is a field I create, or is it part of all records? It is a field you create. Which is why I wrote "Where is the table in question, and is a suitable column (eg a timestamp) to determine what the newest record is." You might be able to use the pseudo-column RDB$RECORD_VERSION, but that is not necessarily in the right order of recency, because the value is the transaction-number that wrote the last version of the record. This means that updates to a record will also make it 'recent' and a record written by a long-running transaction might not show up as 'recent' (or at least as older than it may actually be). Mark -- Mark Rotteveel
Re: [firebird-support] How do I limit an SQL search list to 10 records?
Is a field I create, or is it part of all records? On Fri, Dec 13, 2019 at 12:04 PM Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: > > > On 13/12/2019 16:48, Clyde Eisenbeis cte...@gmail.com [firebird-support] > wrote: > > One table contains history. The old history is never replaced. New > > history is added. What is relevant is the most recent history. Is there > > a way to find the newest records without the KeyID? > > Yes, you do > > SELECT * > FROM > ORDER BY DESC > FETCH NEXT 10 ROWS ONLY > > Where is the table in question, and is a suitable > column (eg a timestamp) to determine what the newest record is. > > If you want that result in ascending order of time, then you need to add > another order by: > > SELECT * > FROM ( > SELECT * > FROM > ORDER BY DESC > FETCH NEXT 10 ROWS ONLY > ) a > ORDER BY ASC > > Or you can use ROW_NUMBER, although that is probably less efficient: > > SELECT * > FROM ( > SELECT .*, > ROW_NUMBER OVER (ORDER BY DESC) AS ROWNR > FROM > ) a > ORDER BY ASC > WHERE ROWNR < 10 > > Mark > -- > Mark Rotteveel > >
Re: [firebird-support] How do I limit an SQL search list to 10 records?
On 13/12/2019 16:48, Clyde Eisenbeis cte...@gmail.com [firebird-support] wrote: > One table contains history. The old history is never replaced. New > history is added. What is relevant is the most recent history. Is there > a way to find the newest records without the KeyID? Yes, you do SELECT * FROM ORDER BY DESC FETCH NEXT 10 ROWS ONLY Where is the table in question, and is a suitable column (eg a timestamp) to determine what the newest record is. If you want that result in ascending order of time, then you need to add another order by: SELECT * FROM ( SELECT * FROM ORDER BY DESC FETCH NEXT 10 ROWS ONLY ) a ORDER BY ASC Or you can use ROW_NUMBER, although that is probably less efficient: SELECT * FROM ( SELECT .*, ROW_NUMBER OVER (ORDER BY DESC) AS ROWNR FROM ) a ORDER BY ASC WHERE ROWNR < 10 Mark -- Mark Rotteveel
Re: [firebird-support] How do I limit an SQL search list to 10 records?
One table contains history. The old history is never replaced. New history is added. What is relevant is the most recent history. Is there a way to find the newest records without the KeyID? On Fri, Dec 13, 2019 at 8:58 AM Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: > > > On 2019-12-13 12:48, Clyde Eisenbeis cte...@gmail.com [firebird-support] > wrote: > > I added the KeyID so I could sort from newest to oldest. Was it > > necessary to add this KeyID, or is there another way to find the > > newest records without the KeyID? > > That depends on what you're trying to achieve, what data you have and > what results you want. > > Mark > >
Re: [firebird-support] How do I limit an SQL search list to 10 records?
On 2019-12-13 12:48, Clyde Eisenbeis cte...@gmail.com [firebird-support] wrote: > I added the KeyID so I could sort from newest to oldest. Was it > necessary to add this KeyID, or is there another way to find the > newest records without the KeyID? That depends on what you're trying to achieve, what data you have and what results you want. Mark
Re: [firebird-support] How do I limit an SQL search list to 10 records?
I added the KeyID so I could sort from newest to oldest. Was it necessary to add this KeyID, or is there another way to find the newest records without the KeyID? On Fri, Dec 13, 2019 at 2:05 AM Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: > > > On 2019-12-13 08:14, Kjell Rilbe kjell.ri...@marknadsinformation.se > [firebird-support] wrote: > > Den 2019-12-12 kl. 22:36, skrev Clyde Eisenbeis cte...@gmail.com > > [firebird-support]: > >> Marcin, Minor tweak => works! I also added DESC to retrieve the > >> newest records using the field key name (number is an auto increment). > >> ` > >> "SELECT FIRST " + stNumOfRecordsToRetrieve + " * FROM " + stTableName > >> + " ORDER BY " + fstKeyID + " DESC"; > >> ` > >> It's interesting that the newest records are found first (DESC), then > >> the number of records retrieved are from those DESC record results.. > > > > > > Yes, the "subset clause" is applied last of all, so specified ordering > > will be respected. > > > > Unless I'm mistaken, FIRST N is Firebird specific. The SQL standard > > syntax for this is: > > > > SELECT * > > FROM T > > ORDER BY YOUR_KEY DESC > > ROWS N; > > > > where N would be the number of rows to retrieve. You also have: > > > > SELECT * > > FROM T > > ORDER BY YOUR_KEY DESC > > ROWS N TO M; > > > > which will retrieve records N, N+1, N+2, ..., M. Useful for pagination > > and not supported, as far as I know, by the Firebird specific syntax. > > The SQL standard (SQL:2008 and higher) clause is [FETCH][1] which was > added in Firebird 3. ROWS is also a Firebird specific invention, or it > might have been something that was in a SQL standard draft, but never > made it in a final standard. > > The SQL Standard OFFSET/FETCH combination is IMHO better, though the > same result can be achieved with the non-standard FIRST and SKIP. > > [1]: > > https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/bk02ch09s06.html#rnfb30-dml-offsetfetch > >
Re: [firebird-support] How do I limit an SQL search list to 10 records?
On 2019-12-13 08:14, Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support] wrote: > Den 2019-12-12 kl. 22:36, skrev Clyde Eisenbeis cte...@gmail.com > [firebird-support]: >> Marcin, Minor tweak => works! I also added DESC to retrieve the >> newest records using the field key name (number is an auto increment). >> ` >> "SELECT FIRST " + stNumOfRecordsToRetrieve + " * FROM " + stTableName >> + " ORDER BY " + fstKeyID + " DESC"; >> ` >> It's interesting that the newest records are found first (DESC), then >> the number of records retrieved are from those DESC record results. > > > Yes, the "subset clause" is applied last of all, so specified ordering > will be respected. > > Unless I'm mistaken, FIRST N is Firebird specific. The SQL standard > syntax for this is: > > SELECT * > FROM T > ORDER BY YOUR_KEY DESC > ROWS N; > > where N would be the number of rows to retrieve. You also have: > > SELECT * > FROM T > ORDER BY YOUR_KEY DESC > ROWS N TO M; > > which will retrieve records N, N+1, N+2, ..., M. Useful for pagination > and not supported, as far as I know, by the Firebird specific syntax. The SQL standard (SQL:2008 and higher) clause is [FETCH][1] which was added in Firebird 3. ROWS is also a Firebird specific invention, or it might have been something that was in a SQL standard draft, but never made it in a final standard. The SQL Standard OFFSET/FETCH combination is IMHO better, though the same result can be achieved with the non-standard FIRST and SKIP. [1]: https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/bk02ch09s06.html#rnfb30-dml-offsetfetch
Re: [firebird-support] How do I limit an SQL search list to 10 records?
On 2019-12-12 19:59, Clyde Eisenbeis cte...@gmail.com [firebird-support] wrote: > This finds all records: > `` > "SELECT * FROM " + stTableName > > `` > > This does not work: > > `` "SELECT * FROM " + stTableName > + " FETCH 10 ROWS" > `` Assuming you are using Firebird 3, you are missing the FIRST (or NEXT) keyword before the number and the ONLY keyword after ROWS. In Firebird 3 and higher you can use the SQL standard fetch clause: SELECT * FROM FETCH FIRST 10 ROWS ONLY See [SQL:2008-Compliant OFFSET and FETCH Clauses][1] In Firebird 2.0 and higher you can use: SELECT * FROM ROWS 10 See [ROWS][2] In Firebird 1.0 and higher you can use: SELECT FIRST 10 * FROM See [FIRST, SKIP][3] Mark [1]: https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/bk02ch09s06.html#rnfb30-dml-offsetfetch [2]: https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-select.html#fblangref25-dml-select-rows [3]: https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-select.html#fblangref25-dml-select-first-skip
Re: [firebird-support] How do I limit an SQL search list to 10 records?
Den 2019-12-12 kl. 22:36, skrev Clyde Eisenbeis cte...@gmail.com [firebird-support]: > Marcin, Minor tweak => works! I also added DESC to retrieve the > newest records using the field key name (number is an auto increment). > ` > "SELECT FIRST " + stNumOfRecordsToRetrieve + " * FROM " + stTableName > + " ORDER BY " + fstKeyID + " DESC"; > ` > It's interesting that the newest records are found first (DESC), then > the number of records retrieved are from those DESC record results. Yes, the "subset clause" is applied last of all, so specified ordering will be respected. Unless I'm mistaken, FIRST N is Firebird specific. The SQL standard syntax for this is: SELECT * FROM T ORDER BY YOUR_KEY DESC ROWS N; where N would be the number of rows to retrieve. You also have: SELECT * FROM T ORDER BY YOUR_KEY DESC ROWS N TO M; which will retrieve records N, N+1, N+2, ..., M. Useful for pagination and not supported, as far as I know, by the Firebird specific syntax. Regards, Kjell [Non-text portions of this message have been removed]
Re: [firebird-support] How do I limit an SQL search list to 10 records?
Marcin, Minor tweak => works! I also added DESC to retrieve the newest records using the field key name (number is an auto increment). ` "SELECT FIRST " + stNumOfRecordsToRetrieve + " * FROM " + stTableName + " ORDER BY " + fstKeyID + " DESC"; ` It's interesting that the newest records are found first (DESC), then the number of records retrieved are from those DESC record results. ` On Thu, Dec 12, 2019 at 1:29 PM 'Marcin Bury' marcin.b...@studio-delfi.pl [firebird-support] wrote: > > > Hi > > SELECT FIRST 10 * FROM A_TABLE > > Should work > > Cheers > Marcin > > > -- Wiadomość oryginalna -- > Od: "Clyde Eisenbeis cte...@gmail.com [firebird-support]" < > firebird-support@yahoogroups.com> > Do: firebird-support@yahoogroups.com > Data: 12.12.2019 19:59:44 > Temat: [firebird-support] How do I limit an SQL search list to 10 records? > > This finds all records: > `` > "SELECT * FROM " + stTableName > `` > This does not work: > > `` > "SELECT * FROM " + stTableName + " FETCH 10 ROWS" > `` > > >
Re: [firebird-support] How do I limit an SQL search list to 10 records?
Hi SELECT FIRST 10 * FROM A_TABLE Should work Cheers Marcin -- Wiadomość oryginalna -- Od: "Clyde Eisenbeis cte...@gmail.com [firebird-support]" Do: firebird-support@yahoogroups.com Data: 12.12.2019 19:59:44 Temat: [firebird-support] How do I limit an SQL search list to 10 records? This finds all records: `` "SELECT * FROM " + stTableName `` This does not work: `` "SELECT * FROM " + stTableName + " FETCH 10 ROWS" ``
