Sort name

[cleanair4me46]

I have a query in Oracle 9i where I sort by name:

select fullname from PersonTable 
order by fullname

It works great but I also need to sort the firstname which appears after a comma:

Baker, Ben A
Collins Jennings, Jerry R
Crewson, Albert W
Davis, Sam S
Davis, Karen W
Miller-Smith, Jan
Miller, Andy

I would like for it to sort where the lastname is first sort and the first name would also be sorted:

Baker, Ben A
Collins Jennings, Jerry R
Crewson, Albert W
Davis, Karen W
Davis, Sam S
Miller, Andy
Miller-Smith, Jan

 

[jdemmi]

You would need to instr/substr the fullname field splitting the field into 2 separate fields 1 to the left and 1 to the right of the ,. (instr to locate the position of the , and substr to take the characters to the left or right of that character respectively)

But I'd almost guarantee that the persontable already has a first name and a last name field. If so, you can simply sort on those. Can you post the table's structure?

http://www.psoug.org/reference/substr_instr.html

-- Jason
"It's Just Ones and Zeros

 

[jdemmi]

Here's a more detailed explanation of what I was getting at....

select substr('Smith, Ken',1,instr('Smith, Ken',',',1)-1), substr('Smith, Ken',instr('Smith, Ken',',',1)+1,length('Smith, Ken'))from dual

run that in a query window. if you want to use it replace 'Smith, Ken' with your table.field.

-- Jason
"It's Just Ones and Zeros

 

[jdemmi]

Oops...and the most important part....your sorting..
.

select substr('Smith, Ken',1,instr('Smith, Ken',',',1)-1) as last_name, substr('Smith, Ken',instr('Smith, Ken',',',1)+1,length('Smith, Ken')) as first_name from dual
order by last_name, first_name

-- Jason
"It's Just Ones and Zeros

 

[Dagon]

I'm slightly confused over why you would be getting "Davis, Sam S" ahead of "Davis, Karen W" if you're genuninely sorting by the whole name. Since the bits up to "Davis, " are identical, it should automatically sort Karen ahead of Sam. Perhaps you have some extra spaces in some of your names.

 

[SantaMufasa]

cleanair4me46,

Did you follow up on Dagon's excellent observation (that you should still get your records in Last-name, First-name order even if you don't parse out the first names from the last names)?

Have you identified the cause for your anomaly?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[cleanair4me46]

Yes, sorry it took so long to find it but it was an XML output and I thought it was coming from Oracle. The web service I subscribe to was using Oracle query. The output is working correctly and I misread it.
Thanks for your time on this.