pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWhat should this query match?
4+ messages / 2 participants
[nested] [flat]
* What should this query match?
@ 2005-09-14 09:54 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-09-14 10:48 ` Re: What should this query match? Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 4+ messages in thread
From: Joost Kraaijeveld @ 2005-09-14 09:54 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: What should this query match?
2005-09-14 09:54 What should this query match? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2005-09-14 10:48 ` Peter Eisentraut <peter_e@gmx.net>
2005-09-14 11:09 ` Re: What should this query match? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 1 reply; 4+ messages in thread
From: Peter Eisentraut @ 2005-09-14 10:48 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@askesis.nl>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: What should this query match?
2005-09-14 09:54 What should this query match? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-09-14 10:48 ` Re: What should this query match? Peter Eisentraut <peter_e@gmx.net>
@ 2005-09-14 11:09 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-09-14 14:39 ` Re: What should this query match? Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 4+ messages in thread
From: Joost Kraaijeveld @ 2005-09-14 11:09 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: What should this query match?
2005-09-14 09:54 What should this query match? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-09-14 10:48 ` Re: What should this query match? Peter Eisentraut <peter_e@gmx.net>
2005-09-14 11:09 ` Re: What should this query match? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2005-09-14 14:39 ` Peter Eisentraut <peter_e@gmx.net>
0 siblings, 0 replies; 4+ messages in thread
From: Peter Eisentraut @ 2005-09-14 14:39 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@askesis.nl>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-09-14 14:39 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-14 09:54 What should this query match? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-09-14 10:48 ` Peter Eisentraut <peter_e@gmx.net>
2005-09-14 11:09 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-09-14 14:39 ` Peter Eisentraut <peter_e@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox