asked on

SQL CALL IN DATABASE FIREBIRD FINDING HIGHEST RECORD FOR 2 COMBINED FIELDS IN VB6

I have database (Firebird) with 3 indices,
key(0) duplicate
key(1) duplicate
key(2) = combination off key(0) and key(1) no duplicate

the needed record in the SQL call is the hiighest value for key (1)

if do e.g. select * from datalist order by key(1), key(0) i will get a list with correct order

sample database
(key(0) (key(1)
00000000006 00000000009
00000000009 00000000009
00000000112 00000000009
00000000341 00000000009
00000000781 00000000009

what would be the syntax in SQL call based on wished highest value for key 0000000009 to find like in this sample
the record 00000000781 00000000009

SOLUTION

Nick Upson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jack niekerk

ASKER

ok, I added screenshot
the record needed als result would for max(1) the record having value
0000000016 , being the last modification

the software adds a new number to every modification on a item
item number is col ARTID, col PRLISID is the counter

same story for max(9)
the record needed as result should be
0000000179
result-and-database-specs.bmp

jack niekerk

ASKER

ok did find out on this call at least in read the last one is the highest record

SELECT * FROM stammpreispos where artid = (select max(9) from stammpreispos) order by prlistid;

So I could loop unitll I have the last one in buffer, would there be a SQL call just to retrieve this last highest record only ?

jack niekerk

ASKER

ok this works, if this is the only way you think , do a loop and no way to jump direct to
last record in loop then this it should be right?

strSQL2 = " SELECT * FROM stammpreispos where artid = (select max(" & SearchFor$ & ") from stammpreispos) order by prlistid;"
Rst2.Source = Trim(strSQL2)
Rst2.Open

Do While Not Rst2.EOF
Debug.Print Rst2.Fields(0), Rst2.Fields(1), Rst2.Fields(2), Rst2.Fields(3), Rst2.Fields(4)
Rst2.MoveNext
Loop
Rst2.Close

ASKER CERTIFIED SOLUTION

Ark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial