Re: select offset by alphabetical reference

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