Home » SQL & PL/SQL » SQL & PL/SQL » Need help on SQL query output
Need help on SQL query output [message #238076] Wed, 16 May 2007 11:04 Go to next message
gi_srinivas
Messages: 19
Registered: September 2005
Location: Hyderan
Junior Member

Hi,

I need help on the following query:

SELECT poh.approved_date APPROVE_date, poh.segment1 last_po_number, rra.receipt_num receipt_num
FROM mtl_system_items_b msi,
po_headers_all poh,
po_lines_all pol,
rcv_receipts_all_v rra,
rcv_transactions rt
WHERE poh.po_header_id = pol.po_header_id
AND rt.shipment_header_id = rra.shipment_header_id
AND msi.inventory_item_id = pol.item_id
AND rt.po_line_id = pol.po_line_id
AND rt.transaction_type = 'RECEIVE'
AND poh.authorization_status = 'APPROVED'
-- AND ROWNUM < 2
and msi.segment1 = '20600-0147'
AND msi.organization_id=271

The output of the above query is as follows:

******************

APPROVE_DATE LAST_PO_NUMBER RECEIPT_NUM

10/16/2005 3:02:48 PM 70503518 820500601
10/12/2006 11:44:23 AM 70603671 820600620
3/16/2005 11:26:38 AM 70500679 820500123
11/30/2005 2:13:51 PM 70504150 820500662

******************

Now my target is to display the record which is having the maximum APPROVE_DATE.

I need the record like this:

************************
APPROVE_DATE LAST_PO_NUMBER RECEIPT_NUM

10/12/2006 11:44:23 AM 70603671 820600620
************************

Can anybody please help me.

Thanks a lot in advance.

Regards,
Srinivas
Re: Need help on SQL query output [message #238082 is a reply to message #238076] Wed, 16 May 2007 11:23 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

select ...
from (
select ..., row_number() over (order by APPROVE_DATE desc) rn
from ...
where ...
)
where rn = 1
/

Regards
Michel
Previous Topic: Questions in joins
Next Topic: setting session timezone in 9i/10g
Goto Forum:
  


Current Time: Sat Dec 10 20:24:34 CST 2016

Total time taken to generate the page: 0.09572 seconds