OraFAQ Forum: SQL & PL/SQL » query of status changed

Home » SQL & PL/SQL » SQL & PL/SQL » query of status changed (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query of status changed [message #612280]

Tue, 15 April 2014 09:43

usmanelahi
Messages: 54
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

Report message to a moderator

Re: query of status changed [message #612282 is a reply to message #612280]

Tue, 15 April 2014 09:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: query of status changed [message #612283 is a reply to message #612280]

Tue, 15 April 2014 09:53

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Use analytic functions LAG and MAX.

SY.

Report message to a moderator

Re: query of status changed [message #612285 is a reply to message #612280]

Tue, 15 April 2014 10:05

Lalit Kumar B
Messages: 3174
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.

Report message to a moderator

Re: query of status changed [message #612312 is a reply to message #612285]

Tue, 15 April 2014 23:55

usmanelahi
Messages: 54
Registered: July 2007
Location: Karachi

Member

hot to get the middle line record from 10 records the status changed on fifth record please suggest

Report message to a moderator

Re: query of status changed [message #612316 is a reply to message #612312]

Wed, 16 April 2014 00:53

Michel Cadot
Messages: 68648
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

usmanelahi
Messages: 54
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

Report message to a moderator