Solved

oracle sql - rank

Posted on 2014-11-24
9
171 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 56

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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 56

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 56

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup
Suggested Courses

623 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