Question:
I need to retrieve the record with the highest value in field PRICE from table PRODUCTS. How can I do this?
Answer:
The straightforward thing is to use the SQL operator MAX():
select MAX(PRICE) from PRODUCTS
This will return the highest price. Unfortunately you won't know which product(s) are associated with this price.
Instead you could retrieve this MAX() value, store it in a variable and then run a second query (here shown in the context of a stored procedure but you may want to do this from Delphi instead):
select MAX(PRICE) from PRODUCTS into :MAX_PRICE;
select * from PRODUCTS where PRICE=:MAX_PRICE
The previous snippet would return multiple rows if there are several products at this same highest price. If you're confident that there will be only one you could go a different route using the ORDER .. DESC clause.
select * from PRODUCTS order by PRICE desc
The downside of this is that depending on your database drivers this will prepare to sort the whole recordset and possibly transfer 100 rows over the network even though you need only one row.
In mysql there is the handy LIMIT clause. InterBase and ORACLE don't have it as far as I know. The next statement will not cause unnecessary network traffic:
select * from PRODUCTS order by PRICE desc LIMIT 1