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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
