Home » SQL & PL/SQL » SQL & PL/SQL » How to get only last 3 ocuurances of OUT_DATE (Merged)
How to get only last 3 ocuurances of OUT_DATE (Merged) [message #358164] Sun, 09 November 2008 22:21 Go to next message
sasi.tati
Messages: 1
Registered: November 2008
Location: Hyderbad
Junior Member

select * from o_audit where o_mat_itm_no = 'NIR-97116'
O_MAT_ITM_NO IN_DATE OUT_DATE IN_STATUS
--------------- --------------------- --------------------- --------------------
NIR-97116 12/17/2005 1:30:00 PM 12/17/2005 1:30:25 PM A1-REGISTERED
NIR-97116 12/17/2005 1:30:00 PM 12/17/2005 1:30:25 PM A1-REGISTERED
NIR-97116 12/17/2005 1:30:25 PM 12/17/2005 2:06:46 PM A2-REG COMPLETE
NIR-97116 12/17/2005 1:30:25 PM 12/17/2005 2:06:46 PM A2-REG COMPLETE
NIR-97116 12/17/2005 2:37:44 PM B5-CPO APPR FOR SAP
NIR-97116 12/17/2005 2:37:44 PM B5-CPO APPR FOR SAP
NIR-97116 12/17/2005 2:06:46 PM 12/17/2005 2:37:44 PM A3-PLANT APPROVED
NIR-97116 12/17/2005 2:06:46 PM 12/17/2005 2:37:44 PM A3-PLANT APPROVED
8 rows selected


select * from o_audit where o_mat_itm_no = 'NIR-97200'
O_MAT_ITM_NO IN_DATE OUT_DATE IN_STATUS
--------------- --------------------- --------------------- --------------------
NIR-97200 12/18/2005 7:11:48 AM 12/18/2005 7:12:09 AM A1-REGISTERED
NIR-97200 12/18/2005 7:11:48 AM 12/18/2005 7:12:09 AM A1-REGISTERED
NIR-97200 12/18/2005 7:12:09 AM 12/18/2005 7:27:49 AM A3-PLANT APPROVED
NIR-97200 12/18/2005 7:12:09 AM 12/18/2005 7:27:49 AM A2-REG COMPLETE
NIR-97200 12/18/2005 7:27:49 AM B5-CPO APPR FOR SAP
NIR-97200 12/18/2005 7:27:49 AM B5-CPO APPR FOR SAP
6 rows selected

By using 'DISTINCT' the result for that 'NIR-97116' AND 'NIR-97200' is

SELECT DISTINCT O_MAT_ITM_NO, IN_DATE, OUT_DATE, IN_STATUS, OUT_STATUS,
IN_TABLE, O_MAND_ATTR, O_IN_MAND_FILL, O_OUT_MAND_FILL, O_TOT_ATTR,
O_IN_TOT_ATTR_FILL, O_OUT_TOT_ATTR_FILL, O_NOUN, O_MODIFIER, O_DESC,
O_DURATION, O_SAP_PLANT, O_USER, SEQ FROM O_AUDIT WHERE OUT_DATE >= OUT_DATE -3 ORDER BY O_MAT_ITM_NO, OUT_DATE DESC

OUTPUT IS::


O_MAT_ITM_NO IN_DATE OUT_DATE IN_STATUS
--------------- --------------------- --------------------- --------------------
NIR-97116 12/17/2005 2:06:46 PM 12/17/2005 2:37:44 PM A3-PLANT APPROVED
NIR-97116 12/17/2005 2:06:46 PM 12/17/2005 2:37:44 PM A3-PLANT APPROVED
NIR-97116 12/17/2005 1:30:25 PM 12/17/2005 2:06:46 PM A2-REG COMPLETE
NIR-97116 12/17/2005 1:30:25 PM 12/17/2005 2:06:46 PM A2-REG COMPLETE
NIR-97116 12/17/2005 1:30:00 PM 12/17/2005 1:30:25 PM A1-REGISTERED
NIR-97116 12/17/2005 1:30:00 PM 12/17/2005 1:30:25 PM A1-REGISTERED



--------------- --------------------- --------------------- --------------------
NIR-97200 12/18/2005 7:12:09 AM 12/18/2005 7:27:49 AM A2-REG COMPLETE
NIR-97200 12/18/2005 7:12:09 AM 12/18/2005 7:27:49 AM A3-PLANT APPROVED
NIR-97200 12/18/2005 7:11:48 AM 12/18/2005 7:12:09 AM A1-REGISTERED
NIR-97200 12/18/2005 7:11:48 AM 12/18/2005 7:12:09 AM A1-REGISTERED

Again i am getting more than 3 records for an O_MAT_ITM_NO.

Actually i want only last 3 occurrences of an O_MAT_ITM_NO with no duplicate OUT_DATE FROM O_AUDIT..

can u please send me the new query for this problem.

[Updated on: Sun, 09 November 2008 23:54] by Moderator

Report message to a moderator

Re: how to use group by over here [message #358166 is a reply to message #358164] Sun, 09 November 2008 22:58 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Try Analytical function

[ Don't multipost;
Form the distinct query then apply analytic function on it ]

Format the posting form next time.


Smile
Rajuvan.

[Updated on: Sun, 09 November 2008 23:57]

Report message to a moderator

Re: how to use group by over here [message #358198 is a reply to message #358166] Mon, 10 November 2008 01:27 Go to previous messageGo to next message
shobanasree
Messages: 9
Registered: November 2008
Location: Bangalore
Junior Member
To select unqiue rows from duplicate rows refer the topic "Selecting unique Rows from Table contains duplicate rows."
Re: How to get only last 3 ocuurances of OUT_DATE (Merged) [message #358207 is a reply to message #358164] Mon, 10 November 2008 02:00 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time, before posting, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: How to use a clob column in group by clause
Next Topic: Selecting uniquw Rows from Table contains duplicate rows.
Goto Forum:
  


Current Time: Sat Dec 10 08:49:18 CST 2016

Total time taken to generate the page: 0.07927 seconds