>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.

Try:

SELECT * FROM bkwplot ;
 WHERE lh_pprice IN (SELECT MAX(lh_pprice) FROM bkwplot)

If several records have identical values for lh_pprice, all equal to the maximum value, all records will appear in the resulting cursor.

>
>Regards,
>Stuart