RE: Help sorting out SQL statement

Forgive me if you've already considered this option and discarded it, but wouldn't a group by combined with a max() aggregate function give you what you want? Example:
SQL> select

  2     a.owner || '."' || a.table_name || '"' as tbl,
  3     max (b.partition_name) as last_partition
  4   from
  5     dba_tables a, dba_tab_partitions b
  6   where
  7     a.owner = 'SPC_TBL_OWNER'
  8     and a.table_name in ('COUNTRY', 'CUST_CATEGORY_TOTAL')
  9     and a.owner = b.table_owner
 10     and a.table_name = b.table_name
 11   group by
 12     a.owner, a.table_name ;

TBL                                                             LAST_PARTITION
--------------------------------------------------------------- ----------------------
SPC_TBL_OWNER."COUNTRY"                                         COUNTRY_PZ
SPC_TBL_OWNER."CUST_CATEGORY_TOTAL"                             CUST_CAT_2001

SQL> -----Original Message-----
Gamini Karunaratne

I need help in sorting out a tricky sql statement as follows: Statement:
SELECT /*+RULE */ distinct rfx.rfx_id , rfx.type_id , supp.supplier_id ,  decode ( nvl(rrfx.state_id, 0) , 1, 'Responded', 3, 'Responded', 6, 'Responded', 7, 'Responded', supp.read_state_id) as full_read_state,  rfx.subject , TO_CHAR(LOG.date_changed, 'DD/MM/YY') , TO_CHAR(LOG.date_changed, 'HH24:MI') , rfx.buyer_document_id , rfx.buyer_org_guid , rfx.buyer_org_unit_display_name , NULL , supp.supplier_guid , supp.supplier_id ,
(SELECT COUNT(rrfx.rrfx_id) FROM tx_rrfx_document rrfx WHERE rrfx.rfx_id = rfx.rfx_id AND rrfx.state_id >= 3 and rrfx.state_id <> 999),
STATE.ORDER_ID , LOG.date_changed , (SELECT COUNT(*) FROM TX_RFX_ATTACHMENT WHERE RFX_ID = rfx.RFX_ID) FROM tx_rfx_document rfx , tx_rfx_supplier supp , tx_rfx_state_log log , TX_RFX_VIEWER_STATES STATE, tx_rrfx_document rrfx WHERE rfx.rfx_id = supp.rfx_id
AND supp.READ_STATE_ID = STATE.READ_STATE_ID AND LOG.rfx_id = rfx.rfx_id
AND supp.supplier_id = rrfx.supplier_id (+) AND log.log_id =(select MAX(log3.log_id) from tx_rfx_state_log log3 where log3.rfx_id = rfx.rfx_id and LOG3.to_state_id IN (SELECT MAX(log2.to_state_id) FROM tx_rfx_state_log log2 WHERE log2.rfx_id = rfx.rfx_id AND (log2.to_state_id = 9 OR log2.to_state_id = 12))) AND supp.read_state_id <> 'Deleted'
AND nvl(rrfx.state_id, 0) <> 999
and rfx.rfx_id = 12619
ORDER BY STATE.ORDER_ID, full_read_state;  

The output I get is as follows:

RFX_ID       TYPE_ID      SUPPLIER_ID   FULL_READ_STATE  SUBJECT   etc etc
12619             1                1207                   Full                        homing
12619              1               1205                   Normal                   homing
12619              1               1209                   Normal                   hourly

How is if possible for me to change the script so that my output is a unique rfx_id and only the maximum supplier_id is output (ie supplier_id 1209)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).