Received: from wallace.ece.rice.edu (root@wallace.ece.rice.edu [128.42.12.154]) by hub.org (8.9.2/8.9.1) with ESMTP id OAA84174 for ; Sun, 18 Apr 1999 14:51:15 -0400 (EDT) (envelope-from reedstrm@wallace.ece.rice.edu) Received: by wallace.ece.rice.edu via sendmail from stdin id (Debian Smail3.2.0.101) for pgsql-sql@hub.org; Sun, 18 Apr 1999 13:51:15 -0500 (CDT) Message-Id: From: reedstrm@wallace.ece.rice.edu (Ross J. Reedstrom) Subject: Re: [SQL] Case insensitive searchs In-Reply-To: from Andy Lewis at "Apr 18, 99 01:31:20 pm" To: alewis@themecca.net (Andy Lewis) Date: Sun, 18 Apr 1999 13:51:15 -0500 (CDT) Cc: pgsql-sql@hub.org X-Mailer: ELM [version 2.4ME+ PL39 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Hmm, what version of postgresql are you using? In my hands, the 'distinct' works on the output of ther function call, as seen in my previous email. You do realize that 'distinct' works on the entire returned row, not just a single field, so, to continue my previous example: test=> select distinct initcap(city),upper(state) from addr_table; initcap |upper -----------+----- San Antonio|TX (1 row) test=> select distinct initcap(city),upper(state),oid from addr_table; initcap |upper| oid -----------+-----+----- San Antonio|TX |25354 San Antonio|TX |25355 San Antonio|TX |25356 San Antonio|TX |25357 (4 rows) test=> select distinct initcap(city),upper(state),oid from addr_table group by initcap(city); initcap |upper| oid -----------+-----+----- San Antonio|TX |25354 (1 row) test=> > The problem is that I don't know all of the city names. > > I was trying to do a: > > select distinct lower(city) from mytable is that _all_ the return fields you specified? Ross -- Ross J. Reedstrom, Ph.D., NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005