Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

oracle sql - rank

Posted on 2014-11-24
9
Medium Priority
?
175 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 59

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 59

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 400 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 1600 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 59

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

Create CentOS 7 Newton Packstack Running Keystone

A bug was filed against RDO for the installation of Keystone v3. This guide is designed to walk you through the configuration for using Keystone v3 with Packstack. You will accomplish this using various repos and the Answers file.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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 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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

722 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