Home » SQL & PL/SQL » SQL & PL/SQL » Ranking Non-Consecutive Rows (Oracle 11G XE)
Ranking Non-Consecutive Rows [message #564440] Thu, 23 August 2012 16:11 Go to next message
ramisy2k
Messages: 150
Registered: April 2005
Senior Member
Hi

I have a table T with columns

Col1    Col2
1        A
2        B
3        C
4        D
5        D
6        A
7        B
8        C
9        C
10      A
11      B
12      C
13      C
14      B
15      A
....
....

I want to do group ranking in desired col3 in such a way that it checks for different values across consecutive rows under col2 and assigns a number to each group. Just when two consecutive rows in col2 have same value then the group ends and the next group starts.

So my desired output is:
Col1   Col2   Col3
1       A     1
2       B     1
3       C     1
4       D     1
5       D     2
6       A     2
7       B     2
8       C     2
9       C     3
10      A     3
11      B     3
12      C     4
13      C     5
14      B     5
15      A     5
....
....

Here you can see that the first four rows under col2 are unique i.e A,B,C,D so col3 assigns this as group number 1. It ends at row 4 becuase row 5 also has value D under column 2. So in other words, each group must have all unique values and there should not be any repetition. For example, see group 3 (under col3) in above desired output; it starts from row 9 and ends at row 11 because row 12 also has value 'C' and the value 'C' has already occurred in group 3 in row 9.

I want to achieve this SQL. I tried using Dense rank but couldn't go through. I want the shortest possible query to acheive this. Thanks.

Ramis
Re: Ranking Non-Consecutive Rows [message #564441 is a reply to message #564440] Thu, 23 August 2012 16:23 Go to previous messageGo to next message
BlackSwan
Messages: 22926
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/

>I want the shortest possible query to acheive this.
why shortest? do you have a problem typing longer SQL?
shortest is not necessarily the fastest.

rows in a table are like balls in a basket.
which ball in the basket is the second ball?

[Updated on: Thu, 23 August 2012 16:24]

Report message to a moderator

Re: Ranking Non-Consecutive Rows [message #564446 is a reply to message #564441] Thu, 23 August 2012 17:00 Go to previous messageGo to next message
ramisy2k
Messages: 150
Registered: April 2005
Senior Member
Dear blackswan

Sorry not to post ddl with the post. I will post asap. I searched on Google but found no clue.

I want shortest as well as fastest because I am to use the query in a sub query on a large table. But being shortest or fastest is not the necessary condition.

No rows are not balls in a basket.

Col1 is the primary ey column.

By consecutive rows I mean order by col1which is primary key.

Thanks.
Re: Ranking Non-Consecutive Rows [message #564452 is a reply to message #564446] Thu, 23 August 2012 23:55 Go to previous message
Michel Cadot
Messages: 59504
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
By consecutive rows I mean order by col1which is primary key


Important thing to give and DDL for the test case would give us.

Have a look at LAG function and/or search for it here, you will find your solution as this is a FAQ.

Regards
Michel


Previous Topic: select statement
Next Topic: Calculating leaves
Goto Forum:
  


Current Time: Fri Oct 31 04:30:40 CDT 2014

Total time taken to generate the page: 0.09396 seconds