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));
