Home » SQL & PL/SQL » SQL & PL/SQL » query of status changed (11g)
query of status changed [message #612280] Tue, 15 April 2014 09:43 Go to next message
usmanelahi
Messages: 53
Registered: July 2007
Location: Karachi
Member

hello gurus

please help to make a query

i have a table which is three columns each account_no has multiple record i want to get a one record of date1 where changed

case 1 (get third record date1)
acc status date1
0047677001 6/26/2008
0047677001 6/26/2008
0047677001 Y 6/30/2008


case 2 (get third record)
acc status date1
0047677002 Y 6/26/2008
0047677002 N 6/26/2008
0047677002 Y 6/30/2008


case 3 (get second record)
acc status date1
0047677002 N 6/26/2008
0047677002 Y 6/26/2008
0047677002 Y 6/30/2008


Please help many thanks
Re: query of status changed [message #612282 is a reply to message #612280] Tue, 15 April 2014 09:52 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: query of status changed [message #612283 is a reply to message #612280] Tue, 15 April 2014 09:53 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
Use analytic functions LAG and MAX.

SY.
Re: query of status changed [message #612285 is a reply to message #612280] Tue, 15 April 2014 10:05 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2444
Registered: May 2013
Location: World Wide on the Web
Senior Member
Is the modified date against all the accounts similar? Looking at your test data posted, I assume that the modified date for the accounts might be same. Please confirm.
Re: query of status changed [message #612312 is a reply to message #612285] Tue, 15 April 2014 23:55 Go to previous messageGo to next message
usmanelahi
Messages: 53
Registered: July 2007
Location: Karachi
Member

hot to get the middle line record from 10 records the status changed on fifth record please suggest
Re: query of status changed [message #612316 is a reply to message #612312] Wed, 16 April 2014 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59408
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What you have to post has already been requested to you in your previous topics and BlackSwan repeats it in all your topics.

[Updated on: Wed, 16 April 2014 09:38]

Report message to a moderator

Re: query of status changed [message #612329 is a reply to message #612316] Wed, 16 April 2014 01:50 Go to previous messageGo to next message
usmanelahi
Messages: 53
Registered: July 2007
Location: Karachi
Member

i want the third record date to below sample where last status change

ACC STATUS VER_NO CHECKER_DT_STAMP
0023644001 N 2 8/25/2007 10:43:16 AM
0023644001 Y 3 6/20/2008 4:30:45 PM
0023644001 N 4 6/20/2008 6:14:10 PM
0023644001 N 5 7/30/2008 4:03:54 PM
0023644001 N 6 8/13/2008 3:02:51 PM
0023644001 N 7 8/29/2008 3:22:44 PM

PLEASE HELP
Re: query of status changed [message #612330 is a reply to message #612329] Wed, 16 April 2014 02:01 Go to previous messageGo to next message
Michel Cadot
Messages: 59408
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please post a test case.

Re: query of status changed [message #612344 is a reply to message #612329] Wed, 16 April 2014 06:17 Go to previous message
Solomon Yakobson
Messages: 2049
Registered: January 2010
Senior Member
All you need is analytic lag, max & min.

SY.
Previous Topic: Date in Words
Next Topic: Missing right paren... subquery trouble
Goto Forum:
  


Current Time: Tue Oct 21 11:49:16 CDT 2014

Total time taken to generate the page: 0.05695 seconds