>>select myGroupField, max(myOtherField) as myOtherField, etc. .... group by 1>
>SELECT DISTINCT shp_air.booking_no, cntnr.qnty, shp_air.vessel_txt, shp_air.pier, shp_air.voyage, ; > shp_air.name_depc, shp_air.depart, shp_air.eta_dest_t, shp_air.fldate1, shp_air.dest_port, ; > shp_air.pier_tm, shp_air.eta_dest_d, shp_air.pier_dt, lots.order_date, ; > lots.shipnm, lots.shipattn, lots.shipphn, lots.shipfax, lots.nat_memo, lots.comments, ; > lots.shpord_nos, lots.pieces, lots.unit_wt, lots.wt_tot, lots.unit_vol, lots.vol_tot, ; > lots.consname, lots.consphn, lots.consfax, lots.consattn, lots.cons_acct, lots.cont_fld, ; > lots.agt_o_name, lots.agt_d_name, lots.orig_ctyst, lots.orig_cntry, lots.ff_agent, lots.sig_off, ; > hwb_chg.chg3_desc, hwb_chg.chg3_pp, hwb_chg.per3, lots.shp_air_id, LOTS.HWBNO_XTND, ; > hwb_chg.chg4_desc, hwb_chg.chg4_pp, hwb_chg.per4, ; > hwb_chg.chg5_desc, hwb_chg.chg5_pp, hwb_chg.per5, ; > hwb_chg.chg6_desc, hwb_chg.chg6_pp, hwb_chg.per6, ; > hwb_chg.chg7_desc, hwb_chg.chg7_pp, hwb_chg.per7, ; > hwb_chg.chg8_desc, hwb_chg.chg8_pp, hwb_chg.per8, ; > lots.ultd_eta_d, lots.ultd_eta_t, lots.ultd_ctyst, lots.dlvry_dt, lots.ultd_cntry ; > FROM shp_air, lots, cntnr, hwb_chg ; > WHERE lots.lots_id = ret_val ; > AND lots.lots_id = hwb_chg.lots_id ; > AND shp_air.shp_air_id = lots.shp_air_id ; > AND cntnr.shp_air_id = shp_air.shp_air_id ; > GROUP BY lots.hwbno_xtnd ; > INTO CURSOR bookingrpt >Why do you need to use DISTINCT and GROUP BY in the same query? It looks to me that this particular select would work without GROUP BY. Otherwise you may go with Mike's suggestion to use derived table to join. I actually wanted originally to suggest this as well and even started to write it in my answer but then thought - what would be the field to join?