Обсуждение: select offset by alphabetical reference

Have a table with Last, First, etc...
wish to create a select to grab everything from table whose last name is
alphabetically greater than 'Smith'

almost like the following (which is obviously incorrect);
    select last,first from mytable order by last offset 'Smith';

ideas on how to handle this at the postgres level rather than grabbing all and
storing it in an array in PHP/Perl for post processing to grab the desired
records?

thanks

Dave

On Wed, 2003-05-07 at 06:50, Dave [Hawk-Systems] wrote:
> Have a table with Last, First, etc...
> wish to create a select to grab everything from table whose last name is
> alphabetically greater than 'Smith'
>
> almost like the following (which is obviously incorrect);
>     select last,first from mytable order by last offset 'Smith';
>
> ideas on how to handle this at the postgres level rather than grabbing all and
> storing it in an array in PHP/Perl for post processing to grab the desired
> records?

SELECT last, first FROM mytable WHERE last > 'Smith' ORDER BY last;


--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Dearly beloved, avenge not yourselves, but rather give
      place unto wrath. For it is written, Vengeance is
      mine; I will repay, saith the Lord. Therefore if thine
      enemy hunger, feed him; if he thirst, give him drink;
      for in so doing thou shalt heap coals of fire on his
      head. Be not overcome of evil, but overcome evil with
      good."      Romans 12:19-21

On Wed, 7 May 2003 01:50:47 -0400, "Dave [Hawk-Systems]"
<dave@hawk-systems.com> wrote:
>Have a table with Last, First, etc...
>wish to create a select to grab everything from table whose last name is
>alphabetically greater than 'Smith'
>
>almost like the following (which is obviously incorrect);
>    select last,first from mytable order by last offset 'Smith';

SELECT last, first FROM mytable WHERE last > 'Smith' ORDER BY last;

Servus
 Manfred

>> Have a table with Last, First, etc...
>> wish to create a select to grab everything from table whose last name is
>> alphabetically greater than 'Smith'
>>
>> almost like the following (which is obviously incorrect);
>>     select last,first from mytable order by last offset 'Smith';
>>
>> ideas on how to handle this at the postgres level rather than
>grabbing all and
>> storing it in an array in PHP/Perl for post processing to grab the desired
>> records?
>
>SELECT last, first FROM mytable WHERE last > 'Smith' ORDER BY last;

Thanks...

has tried that earlier, but mistakenly forgotten to upper case the first S, and
the results I got were obviously less than expected.  Some sleep and your
confirmation that I was on the right track led to better results.

Dave

On Wed, 7 May 2003, Dave [Hawk-Systems] wrote:

> >> Have a table with Last, First, etc...
> >> wish to create a select to grab everything from table whose last name is
> >> alphabetically greater than 'Smith'
> >>
> >> almost like the following (which is obviously incorrect);
> >>     select last,first from mytable order by last offset 'Smith';
> >>
> >> ideas on how to handle this at the postgres level rather than
> >grabbing all and
> >> storing it in an array in PHP/Perl for post processing to grab the desired
> >> records?
> >
> >SELECT last, first FROM mytable WHERE last > 'Smith' ORDER BY last;
>
> Thanks...
>
> has tried that earlier, but mistakenly forgotten to upper case the first S, and
> the results I got were obviously less than expected.  Some sleep and your
> confirmation that I was on the right track led to better results.

Another issue you might run into having someone with a name like:

von Tropp

as a last name.  If you want the where and order by to be non-case
sensitive, (i.e. von Tropp comes after Smith but before Zenu) you can
change the query to this:

SELECT last, first FROM mytable WHERE lower(last) > 'smith' ORDER BY
lower(last);

As you can guess I work somewhere with a few folks who's last names start
with lower case letters.

Note that you can then index on this as well:

create index mytable_last_lower_dx on mytable (lower(last));

> Another issue you might run into having someone with a name like:
>
> von Tropp
>
> as a last name.
Being picky this isn't a last name. "Tropp" is. "von" (also
de, van, d', etc.) is a modifier best stored in it's own column.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346