[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

oracle sql - rank

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
tonMachine100
Asked:
tonMachine100
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
sdstuberCommented:
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
 
tonMachine100Author Commented:
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
 
HainKurtSr. System AnalystCommented:
here it is:

select g.*, row_number() over (partition by client_id order by one_line_address) as address_id
from guardians g
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
HainKurtSr. System AnalystCommented:
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
 
awking00Commented:
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
 
sdstuberCommented:
>>> wouldnt row number always give each row (within the partiton) a distinct number?

yes

>>>  i dont want that.

what do you want?
0
 
sdstuberCommented:
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
 
HainKurtSr. System AnalystCommented:
what is inv_id? to me it looks like currency :)

did you try my suggestions ^^^ ?
0
 
tonMachine100Author Commented:
great thanks all
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now