Solved

oracle sql - rank

Posted on 2014-11-24
9
159 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 73

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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 31

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 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now