Solved

oracle sql - rank

Posted on 2014-11-24
9
166 Views
Last Modified: 2014-11-24
i have a list of clients and their guardians.

Capture.PNG
i want some sql which will generate the values in column address_no which is the unique identifier for that address for the client

i tried

rank() over (partition by  inv_id, one_line_address order by inv_id, one_line_address)  

Open in new window


but it just returned a 1 in every row. any help is appreciated
0
Comment
Question by:tonMachine100
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40462543
use row_number instead of rank

you should also change your order by.

it doesn't make sense to order by the same columns in the partition because all rows in that partition will have the same values.

try

order by guardian_name
0
 

Author Comment

by:tonMachine100
ID: 40462595
row_number() over (partition by  inv_id, one_line_address order by  inv_id, one_line_address)

Open in new window



outputs:

Capture2.png
wouldnt row number always give each row (within the partiton) a distinct number? i dont want that.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40462598
here it is:

select g.*, row_number() over (partition by client_id order by one_line_address) as address_id
from guardians g
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 51

Expert Comment

by:HainKurt
ID: 40462602
if you dont want uniqe id per client, then no need for partition

select g.*, row_number() over (order by client_id, one_line_address) as address_id
from guardians g
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 100 total points
ID: 40462718
Try dense_rank -
select client_id, client_name, guardian_name, one_line_address, inv_id,
dense_rank() over (partition by client_id order by one_line_address) address_no
from guardians;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40462722
>>> wouldnt row number always give each row (within the partiton) a distinct number?

yes

>>>  i dont want that.

what do you want?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 400 total points
ID: 40462798
I think I understand the problem now.

change your over clause

(partition by inv_id order by one_line_address)

and you use rank or dense_rank
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40462828
what is inv_id? to me it looks like currency :)

did you try my suggestions ^^^ ?
0
 

Author Closing Comment

by:tonMachine100
ID: 40462834
great thanks all
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question