Locate last record with specified key

[AndrewMozley]

I want to find the latest record (if it exists) for a specified account in a table where the fields include the Account code and the transaction date. It happens that I have an index on the table with key : Account + DTOS(Transaction date). What is the easiest way to do this? Is there perhaps an SQL statement.

Thank you.

 

[Mike Lewis]

Assuming that by "latest" you mean the highest:

[tt]SELECT MAX(Account + DTOS(Transaction date)) FROM TheTable[/tt]

That ought to do it.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

or SET ORDER TO tagname (whatever your index key is named) and then GO BOTTOM.

Remember GO TOP and GO BOTTOM respect the order you set, they don't locate for RECNO()=1 or RECNO()=RECCOUNT(), they only do so, if no index is set as order.

Bye, Olaf.

 

[Olaf Doschke]

There is an error in both Mike and my logic, as you'll get the latest record for the max account, not for some account you specify.

In case of using the index with set order, this'll do the trick:

SET ORDER TO tagname DESCENDING
SEEK "00001" && or whatever account

You will arrive at the latest record, if FOUND() is .T.
If FOUND() is .f. - on the other hand - no record for that account exists.

In Mikes case, you need to add a where clause: WHERE account="00001", otherwise you also get the max value of the max account number only.

Bye, Olaf.

 

[Mike Lewis]

Good point, Olaf. I hadn't taken in that it was for a specified account.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[AndrewMozley]

Thank you both; that does the trick. I had forgotten about

SET ORDER TO tagname DESCENDING
SEEK "00001" && or whatever account

... . had thought that there might be a magic SQL command which achieves the result. I notice that after executing the command, the table is positioned just after the record I am seeking - which (understandably) is not an exact match. I don't mind - can check for suitability, then do SKIP -1, but is there a variant of the command ORDER DESCENDING to select the latest record [in this case by date (= most recent)] for this account.

 

[DSummZZZ]

With your index built on Account + DTOS(Transaction date), setting order to DESCENDING along with SET NEAR ON followed by SEEK(Account) without the date should take you to the newest record.



-Dave Summers-

Even more Fox stuff at:

http://www.dave-summers.net/foxprolinks.htm

 

[Olaf Doschke]

AS DSummZZZ says, even with SET NEAR OFF this will put you on the last record in transaction date order. Indeed NEAR ON wouldn't ever put you in EOF and FOUND() would never be .F., as NEAR puts you at the nearest match, eg the next account number, whcih you most probably don't want. Beside Mike already gave you the "magic" SQL.

Can you show your data, an example where your SEEK hits one off?
Is there a case of two transactions at the same date? DTOS will not take the time portion, so your index order might not be chronologically correct within a date. Then you can't use DTOS(transactiondate), you need TTOC(transactiondatetime,1)

There is an even simpler SQL query, sql is quite natural in formulating:
[pre]Select MAX(transactiondatetime) as latesttransactiondatetime FROM transact.dbf WHERE account="00001"[/pre]

I don't know how much nearer to a natural question a command could become.

This query will not make use of your index, but would profit from separate indexes on account and transactiondatetime, it could even make use of both at the same time. Something a SEEK can't.

Bye, Olaf.

 

[Mike Lewis]

had thought that there might be a magic SQL command which achieves the result.

Well, I don't know if I'd call it magic, but the SQL command I showed you will do the trick. Or, a simpler version:

[tt]SELECT MAX(Transaction date) FROM TheTable WHERE Account = "123"[/tt]

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

I would never have thought of those approaches.

I would set near on, and then seek a value 1 bit higher than the desired key and then
skip back one and if the key matches - you have the record... very quickly indeed.

Not as elegant as the select though

? GetLast("001")

Function GetLast()
    Parameters m.KeyValue
    Private m.KeyValue,M.Flag
    m.Flag = .f.
    Select MyTable
    Set order to MyKeyField
    set near on
    seek Just_Past(m.KeyValue)
    set near off
    if !bof()
       Skip -1
    endif
    IF MyKeyField = m.keyValue
        m.Flag = .t.
    endif
    return(m.Flag)

FUNCTION JUST_PAST
	PARAMETERS m.STRING
	PRIVATE m.STRING
	m.STRING = LEFT(m.STRING,LEN(m.STRING)-1)+CHR(ASC(RIGHT(m.STRING,1))+1)
	RETURN(m.STRING)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[Olaf Doschke]

Before we theorize this too much, Andrew could really help clearing the confusion by providing some sample data and why he didn't reached the correct record.

But if you set order DESCENDING, the first record you find with seek of a partial key is the last in order. SET NEAR is not needed and should not be used, Think of the case you SEEK for a non existing account and have SET NEAR ON, then FOUND() will be .F. but you will not be at EOF(), instead the next best match is where you end up. That will be totally off and at the next best account. That's wrong, if you want to end up at EOF for non existing accounts.

SET NEAR is not needed to search for a partial index key, also in the normal SET NEAR OFF mode SEEK makes use of VFPs partial string comparison. That's the core reason you can do where clauses as WHERE LastName="A" and find the first person with a last name starting with A. Merely changing the order from ASCENDING to DESCENDING you find the first or last transaction, if you only search for the account number. That's all there is to it.

DTOS just might not be enough in case transactiondate is datetime. But whatever type the transactiondate is, date or datetime, MAX(transactiondate) is the max transactiondate of all records matchiing the WHERE clause, therefore that simple SQL. The SQL just will not put you at that record, if that's your final goal.

Bye, Olaf.

 

[DSummZZZ]

SET NEAR is not needed to search for a partial index key

I have to disagree. If the index is created using account and DTOS() you will either need SET NEAR OFF, otherwise the seek will hit EOF() because the partial string doesn't match the key. Or you could use SET EXACT OFF. That would probably work too, I just prefer NEAR.


-Dave Summers-

Even more Fox stuff at:

http://www.dave-summers.net/foxprolinks.htm

 

[Olaf Doschke]

I see we're in fact discussing a philosophy here.

EXACT is indeed influencing partial key matching, but EXACT should always be OFF, you can do exact matches by == operation and to seek an exact value, simply seek for a full key expression. String matching also is exact in case of EXACT OFF, if both strings have same length. So you only have advantages with EXACT OFF, ON is not needed at all.

If you want to find the latest transaction in an index on account+DTOS(date) (or Account+TTOC(datetime,1)), then EXACT has to be OFF, of course, anything working without partial key matching is an unnecessry overhead, then you have to add something for the DTOS portion and don't know what key exists. In that case SEEKing account+"99991231" (DTOS) or account+"99991231235959" (TTOC,1) would find the latest transaction with order set DESCENDING and NEAR ON. You're right about that, but with EXACT OFF you simply SEEK account and are at the last record for that account, and if you seek an account with no transactions yet, you go to EOF() with NEAR OFF, I prefer that instead of landing at any other account. Why make it any harder?

I always work with EXACT OFF and NEAR only ON, if I really want to land at a nearest result even without the partial matching.

You're giving away a big advantage with EXACT ON. "Begins with" matches are surely above 90% of all matches you also want to have with WHERE or FOR clauses.

Bye, Olaf.