CODEWITHSUNDEEP
sqloracle-databaseindexing
Frank
Frank

Reputation: 1345

Oracle SQL : How to get same index for the same row values?

I may not state the question clearly in the title, here is what I want:

I got a customer table like this : 

CustNo    Name     State   TaxID 

10001     Tom      CA      12354
10001     Tom      CA      12355
10001     Tom      CA      12356
10002     Jack     IL      12354
10002     Jack     IL      12355
10002     Jack     IL      12356
10002     Jack     IL      12357
10002     Jack     IL      12358
10003     Eric     TX      12356
10003     Eric     TX      12359

Now I want to write a query in Oracle 10G DB to get a report like this :

  Index  CustNo    Name     State   TaxID 

   1     10001     Tom      CA      12354
   1     10001     Tom      CA      12355
   1     10001     Tom      CA      12356
   2     10002     Jack     IL      12354
   2     10002     Jack     IL      12355
   2     10002     Jack     IL      12356
   2     10002     Jack     IL      12357
   2     10002     Jack     IL      12358
   3     10003     Eric     TX      12356
   3     10003     Eric     TX      12359

The rule is obvious: the same CustNo should have the same Index.

Please try the query here if it's possible : http://sqlfiddle.com/#!4/7da53/1

Upvotes: 0

Views: 115

Answers (2)

Frank
Frank

Reputation: 1345

select dense_Rank() over (order by custno) as cIndex
     , CustNo
     , name
     , state
     , taxid 
from customer;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270351

You are looking for dense_rank():

select dense_rank() over (order by CustNo) as theindex,
       t.*
from table t;

Upvotes: 1

Related Questions