Postgres Pro
Downloads
Home   >   mailing lists

Thread: What should this query match?

What should this query match?

From
Joost Kraaijeveld
Date:
Hi,


Should the follwing query match a record with just "Jan" in the column
(there are no nulls in the column and there are 2 records with only
"Jan" in it) ?

SELECT COUNT(*) FROM customers 
WHERE lower(lastName) < lower('Jan%')

If so, and if the result of the previous question  is "144660", should
the next query display "Jan"?

SELECT lastname FROM prototype.customers 
ORDER BY lower(lastname)
LIMIT  1 OFFSET 144600

Why do the following queries return the same count (may be related to
the first question)

SELECT COUNT(*) FROM customers 
WHERE lower(lastName) < lower('Jan%')

SELECT COUNT(*) FROM customers 
WHERE lower(lastName) <= lower('Jan%')

TIA


-- 
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl

Re: What should this query match?

From
Peter Eisentraut
Date:
Am Mittwoch, 14. September 2005 11:54 schrieb Joost Kraaijeveld:
> Why do the following queries return the same count (may be related to
> the first question)
>
> SELECT COUNT(*) FROM customers
> WHERE lower(lastName) < lower('Jan%')
>
> SELECT COUNT(*) FROM customers
> WHERE lower(lastName) <= lower('Jan%')

These queries should return the same answer if the query does not contain the 
value 'Jan%' (or differently capitalized versions).  I suspect that you are 
actually attempting to use % as a wild card, but none of your queries use a 
pattern matching operator, so I'd go back and check if your queries really 
are what you think they should be.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

Re: What should this query match?

From
Joost Kraaijeveld
Date:
On Wed, 2005-09-14 at 12:48 +0200, Peter Eisentraut wrote:

> These queries should return the same answer if the query does not contain the 
> value 'Jan%' (or differently capitalized versions).  I suspect that you are 
> actually attempting to use % as a wild card, but none of your queries use a 
> pattern matching operator, so I'd go back and check if your queries really 
> are what you think they should be.
Indeed, I am looking for a wildcard query, getting all records that are
smaller than lowercase Jan*. From your remark I gather that this is not
the correct syntax ;-).

Could you give me an example? I understand that the following query
returns all matches but I want everything smalle than the match

SELECT COUNT(*) FROM prototype.customers 
WHERE lower(lastName) ilike 'jan%' 

TIA
-- 
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl

Re: What should this query match?

From
Peter Eisentraut
Date:
Am Mittwoch, 14. September 2005 13:09 schrieb Joost Kraaijeveld:
> Could you give me an example? I understand that the following query
> returns all matches but I want everything smalle than the match

If you need that then you will need to use two separate conditions, one for 
less-than and one for LIKE.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/