Just to follow up on the speed of using SELECT with LIMIT option with OFFSET
and ROW_COUNT versus the "old" way of WHERE COUNT > nnnn
I have used my new method in earnest a few times now and the speed seems to
be significantly quicker - despite the extra overhead of creating a temp
table.
Regards,
Alastair.
----- Original Message -----
From: "Alastair Burr" <[EMAIL PROTECTED]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Saturday, November 08, 2008 9:17 AM
Subject: [RBASE-L] - Re: SELECT LIMIT with WHERE clause?
Hi Albert,
Yes, I tried the TOP option first - even though I wanted the last n rows -
with the data sorted DESC but my ORDER BY clause properly ordered the data
rather than giving me the last n rows added.
The LIMIT option with OFFSET and ROW_COUNT gives me exactly what I want. I
know the number of rows I want and, obviously, can obtain the total number
and subtract to get the offset.
However, I wanted also to avoid including some of those rows selected from
the OFFSET onwards.
The syntax diagram implies that you cannot use a WHERE or ORDER BY clause
since there are no "..." at the end of it and, what's more doing so gave
me
error messages - hence my assumption.
I have gone along the temp table route - it's so easy - but I did discover
something that I probably should have noticed beforehand:
When I was testing at the R:> I simple copied and pasted the appropriate
example and amended it for my needs. When I wrote the code into my command
file I amended further just to reduce the amount of data being loaded.
The syntax specifies "SELECT * FROM tablename..." but I wrote my code to
select just one column since I only needed one column in the temp table.
When I tested I got the wrong results. It was clear that the data had been
sorted by the PK. I tried another column. That one had an index so it got
sorted. I found a column with no index and that returned the correct data.
I
amended my temp table to two columns - one with no index and the column I
really wanted. That worked. I then went back to look at the help and
SELECT
"ALL" FROM with the * registered.
I ended up with this:
INSERT INTO `Temp_Table` SELECT Rec_Date, Rec_Number +
FROM Vinyl LIMIT &vCount, 999999
DEL ROWS FROM `Temp_Table` WHERE Rec_Number IN +
(SELECT Rec_Number FROM Vinyl WHERE Rec_Title CONTAINS ';')
(vCount is the calculated number from which to start.)
The Vinyl table is rather wide with lots of columns so I really didn't
want
to create a matching temp table although I have made a note to remind me
if
a future version tightens up the way the command works.
The other thing to note is that this seems to be faster than my old method
of using "WHERE COUNT >= .vCount". Maybe R:Base has less to do if it knows
the starting point and there are lots of rows in the table.
Regards,
Alastair.
----- Original Message -----
From: "Albert Berry" <[EMAIL PROTECTED]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Friday, November 07, 2008 11:49 PM
Subject: [RBASE-L] - Re: SELECT LIMIT with WHERE clause?
Alastair, the LIMIT comes first. If you are in 7.6, you can return the
TOP so many, and this happens after the WHERE clause.
Alastair Burr wrote:
Hi,
I'm trying to edit the last n rows of a table using the (newish) LIMIT
option and it works just fine, of course.
My reading of the help file is that I cannot also use a WHERE clause.
Is this right or is there a way to reduce the rows returned still
further?
If not I can easily create a temp table and work from that but if
there's an easier way...
Thanks in advance,
Regards,
Alastair
----------------------------
Alastair Burr
St. Albans, UK.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
-----------------------------
No virus found in this incoming message.
Checked by AVG - http://www.avg.com
Version: 8.0.175 / Virus Database: 270.9.0/1773 - Release Date: 07/11/2008
09:08
--- RBASE-L
================================================
TO POST A MESSAGE TO ALL MEMBERS:
Send a plain text email to [email protected]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: INTRO
================================================
TO UNSUBSCRIBE:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: UNSUBSCRIBE
================================================
TO SEARCH ARCHIVES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: SEARCH-n
(where n is the number of days). In the message body,
place any
text to search for.
================================================
--------------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG - http://www.avg.com
Version: 8.0.175 / Virus Database: 270.9.0/1774 - Release Date: 07/11/2008
19:58
