Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
Last records |
Wed, Jun 25 2008 8:37 AM | Permanent Link |
"Uli Becker" | What is the most efficient way to get the last 10 records of a (very large)
table? Uli |
Wed, Jun 25 2008 9:13 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Uli
Partly it depends on what you want to do when you've got them, and if you want a resultset with only 10 records in it. I assume there is an index that can be used to limit the records retrieved. For now I'll assume its datetime and you're dealing with a load of transactions and you just want to look at the last 10, and that you want a resultset with only 10 records. Given the above you do a bit of cheating: 1. CREATE TABLE AS SELECT recordID, transactiondatetime from bigtable where transactiondatetime > roughguess WITH DATA; if its less than 10 records guess again a bit longer ago, if exactly 10 skip the next step 2. create a descending index on the table using transactiondatetime 3. start at the bottom and delete until only 10 records left 4. do a select on your big table with the WHERE clause picking out just those records with their id in the temporary table. Of course you can also just select everything you want into the temporary table. If there's a descending index on the appropriate column you could just use it and the RANGE command. You could also just try the RANGE command it may be fast enough for you. All you need is the recordcount and something like SELECT * FROM bigtable RANGE 123456 to 123466 Roy Lambert [Team Elevate] |
Wed, Jun 25 2008 9:58 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Uli,
<< What is the most efficient way to get the last 10 records of a (very large) table? >> I assume that you want this via SQL ? If so, the best way is a sensitive result set from: SELECT * FROM MyTable RANGE 599990 TO 600000 ElevateDB can do ranges on sensitive result sets very quickly, especially if the ranges are near the start or end of the result set. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Jun 25 2008 11:10 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>SELECT * FROM MyTable >RANGE 599990 TO 600000 Is there a way to use ROWCOUNT in an expression like this? eg SELECT * FROM MyTable RANGE ROWCOUNT-10 TO ROWCOUNT I tried to simulate this with a function select * from companies range rowsintable('companies') - 10 to rowsintable('companies') but received an error ElevateDB Error #700 An error was found in the statement at line 2 and column 32 (Expected end of expression but instead found -) FUNCTION "RowsInTable" (IN "TableName" VARCHAR COLLATE ANSI) RETURNS INTEGER BEGIN DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt; PREPARE InfoStmt FROM 'SELECT * FROM '+ TableName; OPEN InfoCursor; RETURN ROWCOUNT(InfoCursor); END Roy Lambert [Team Elevate] |
Wed, Jun 25 2008 1:15 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< Is there a way to use ROWCOUNT in an expression like this? eg >> I'll see about expand the RANGE clause to use something more than simple values and parameters. There was a reason for doing it this way, but I'll have to look at it again to see why. In the meantime, what you want to do is parameterize (ise the RANGE, like this: BEGIN DECLARE MyCursor CURSOR FOR MyStmt; PREPARE MyStmt FROM 'SELECT * FROM MyTable RANGE ? TO ?'; OPEN MyCursor USING RowsInTable('MyTable')-10, RowsInTable('MyTable'); END -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Jun 25 2008 3:39 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>I'll see about expand the RANGE clause to use something more than simple >values and parameters. There was a reason for doing it this way, but I'll >have to look at it again to see why. I have an excuse for forgetting - I'm old and knackered - what's your reason? Roy Lambert |
Wed, Jun 25 2008 3:54 PM | Permanent Link |
"Uli Becker" | Tim and Roy,
thanks to both. The range will do the job. Uli |
Thu, Jun 26 2008 10:49 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< I have an excuse for forgetting - I'm old and knackered - what's your reason? >> Trust me, if you had to remember and keep track of the amount of information that I do on a daily basis, you'd go insane. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |