Home » SQL & PL/SQL » SQL & PL/SQL » Analytics functions help (10 G)
Analytics functions help [message #338712] Tue, 05 August 2008 15:05 Go to next message
neha_garg123
Messages: 7
Registered: July 2008
Junior Member
Hi I have a table like following

column1 column2 column3 coloumn4 start_Date

1 601 A B 10-jan-2007
2 601 A B 11 -jan-2007
1 602 A B 12-jan-007
1 603 A C 12-jan-2007

there is no Uk on this table.
now I have to group column2 ,column3 ,column4.
I finally need to get the row that has highest start date. Effectively meaming
there will be three groups based on abouve data:


1st is:

1 601 A B 10-jan-2007
2 601 A B 11 -jan-2007

2nd is

1 602 A B 12-jan-007

3rd is
1 603 A C 12-jan-2007

now i need to get the second row from the first group only
2 601 A B 11 -jan-2007

i dont need data from other groups since they have less than 2 rows.

How can I achieve this using analytics function.

Please help!!
Re: Analytics functions help [message #338713 is a reply to message #338712] Tue, 05 August 2008 15:16 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Please use <code tags> to provide understandable information.
Re: Analytics functions help [message #338807 is a reply to message #338712] Wed, 06 August 2008 00:16 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

As mentioned earlier in post please read & follow posting guidelines and also post the test data...

Whereas your solution goes like...


SQL> create table test2 
  2     (column1 varchar2(2),
  3     column2 varchar2(4),
  4     column3 varchar2(2),
  5     column4 varchar2(2),
  6     start_date date);

Table created.

SQL>  insert into test2
  2     values('1','601','A','B',sysdate -1);

1 row created.

SQL>    
SQL>    insert into test2
  2     values('2','601','A','B',sysdate);

1 row created.

SQL>    
SQL>    insert into test2
  2     values('1','602','A','B',sysdate +1);

1 row created.

SQL>    
SQL>    insert into test2
  2     values('1','603','A','C',sysdate +1);

1 row created.

SQL> select * from test2;

CO COLU CO CO START_DAT
-- ---- -- -- ---------
1  601  A  B  05-AUG-08
2  601  A  B  06-AUG-08
1  602  A  B  07-AUG-08
1  603  A  C  07-AUG-08


SQL> SELECT * FROM
  2  (
  3  SELECT ROW_NUMBER() OVER (partition by column2 order by column2, column3) rr, 
  4     column1,
  5     column2,
  6     column3,
  7     column4,
  8     start_date
  9  from test2   
 10  )
 11  where rr=2;

        RR CO COLU CO CO START_DAT
---------- -- ---- -- -- ---------
         2 2  601  A  B  06-AUG-08



Re: Analytics functions help [message #338818 is a reply to message #338712] Wed, 06 August 2008 00:57 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Depending on your definition of "second" (what happens in case of same date?), have a look at ROW_NUMBER, RANK and DENSE_RANK functions.

Regards
Michel
Previous Topic: Hetergenous services
Next Topic: Exception handling
Goto Forum:
  


Current Time: Wed Dec 07 07:04:45 CST 2016

Total time taken to generate the page: 0.08687 seconds