Re: Sorting

[Andrew Zitelli <Andy.Zitelli@TUS.SSI1.COM>]

Benjamin Le wrote:

> How do you sort the last word in a field which has first name, middle
> (some none middle) and last name separated by space in the begin-select
> query:
>
> begin-procedure main
> begin-select
> shbdipl_name    &diploma_name
>   print &diploma_name (+1,1)
> from saturn.shbdipl
> where ...
> ORDER BY ???
> end-select
> end-procedure
>
> SHBDIPL_NAME is student full name field. What I want is sort that field by
> the last word (last name).  I'm really struggling with this. Your help is
> appreciated.  Example:
>    Benjamin Le
>    John B. Adams
>    Smith J. Johnson
> How do you sort by Le, Adams and Johnson?

In Oracle, the SQL syntax would be:

    select substr (name, (instr (name, ' ', -1) + 1)) last_name,
       name from x where name is not null order by last_name;

I haven't tried to use anything like this in SQR.  If you are unable to
easily do this directly, SQR can easily create a database view based on this
query, generate the desired listing, and then delete the view.

The instr function will return the position of the last blank, or zero if
there is none.  This will fail for names like "Benjamin Le, Jr." where it
will sort on the "Jr.".  If you are using an underlying database, the
appropriate structure for names is to break out first, middle and last names,
as well as titles as separate fields.  It is easier to concatenate them
when needed than to automatically break them out into separate fields when
needed.

I hope this is useful.

-- Andy Zitelli, Silicon Systems, Inc.