Thread: 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