Help sorting out SQL statement

[Gamini Karunaratne]

Title: Message

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)

Any help much appreciated.

Gamini

 

 

Alphawest Disclaimer --------------------------------------------------------------------------- If this communication is not intended for you and you are not an authorised recipient of this email you are prohibited by law from dealing with or relying on the email or any file attachments. This prohibition includes reading, printing, copying, re-transmitting, disseminating, storing or in any other way dealing or acting in reliance on the information. If you have received this email in error, we request you contact Alphawest immediately by returning the email to [EMAIL PROTECTED] and destroy the original. This email is confidential and may contain privileged client information. Alphawest has taken reasonable steps to ensure the accuracy and integrity of all its communications, including electronic communications, but accepts no liability for materials transmitted. ---------------------------------------------------------------------------