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
BIAPROAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick UpsonPrincipal Operations EngineerCommented:
select * from mytable where field1 = (select max(field1) from mytable);

I think that does what you want, if not please be more explicit
0
BIAPROAuthor Commented:
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
0
BIAPROAuthor Commented:
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 ?
0
BIAPROAuthor Commented:
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
0
ArkCommented:
SELECT FIRST 1 * FROM ... ORDER BY prlistid DESC
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.