choosing the last record

[krappleby025]

hi all,
I have a query that retrieves records based on an account number, now, sometimes their is no information and sometimes their is, when their is i need to select the last record, in the database so that i can work with it, here is the query i am using

dim invoices
set invoices = con.execute("SELECT * FROM invoices where accountnumber=" & Accountnumber )

now to get the last record i used

do until invoices.EOF
invoices.movelast

this is not working, what i need to do is, if their are no records retrieved then,

a variable for date for example will be set say 1-1-2001

if a record is retrieved then i need to select the last record, and retrieve the date from it,

the problem i am having is selecting the last record....

any ideas on how i can do that

thanks

 

[glendsj]

if you only need to grab the last record:

a: refine the query to only grab the last record.

b: loop until end of file, filling variables holding the data you want displayed. The data from the last record should be in the variables.

 

[BigMag]

Last record:

define a column, with a timestamp.

when you insert a record, insert current timestamp.

when you want the last record:

SELECT A, B, C, MAX(TIMESTAMP) FROM TABLE,
INTO a, b, c,
WHERE <your clause>
GROUP BY A

(Be sure you know what the GROUP BY means)

ore:

SELECT * FROM TABLE
INTO DCLGEN.*
WHERE <your clause>
AND
TIMESTAMP = SELECT MAX(TIMESTAMP) FROM TABLE

One IO, no UNTIL.

Use index on timestamp and it's also fast.

Greetings,

mag

BigMag, The Netherlands.
someone@euronet.nl (no kidding!)