>I would like to know how i can get the record in a table with a certain value that is the maximum.
>
>I have the following data in a table:
>
>lh_recno lh_porg lh_price lh_bales
>700555 360010A 3180.00 4
>700255 360010A 3480.00 5
>700545 360010A 1180.00 1
>700534 360010A 5180.00 12
>700553 360010A 3340.00 2
>700531 360010A 310.00 4
>700111 360010A 180.00 4
>
>I want to be able to retreive the following record:
>700534 360010A 5180.00 12
>
>I have use sql as follows:
>
>select bkwplot.lh_recno, lh_porg, max(lh_pprice) ;
>from bkwplot;
>group by 2
>
>However, it does not return that record. It returns the correct max price, which is 5181, but the rest of the data is incorrect. Data returned would be
>for example:
>
>700531 360010A 5180.00 4 .
>
>How do i get all the fields of the record with a the maximum price and not a mixed return value.
>
>Regards,
>Stuart

lh_recno has no function and would always be the last one when you use aggregate functions in SQL. You can use 'in' as Ed suggested. Also you cn do this :

select * ;
from bkwplot a ;
where a.lh_price = ;
(select max(lh_price) from bkwplot b where a.lh_porg = b.lh_porg)

Cetin