Skip site navigation (1) Skip section navigation (2)

Re: Bug #503: case and LIMIT not working together

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: candrsn(at)mindspring(dot)com, pgsql-bugs(at)postgresql(dot)org
Subject: Re: Bug #503: case and LIMIT not working together
Date: 2001-10-30 20:25:07
Message-ID: 2307.1004473507@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
pgsql-bugs(at)postgresql(dot)org writes:
> case and LIMIT not working together

> when I count ( case ... ) LIMIT
> The count is from the entire table not from the LIMIT
> in the below example the count is returned with the
> same value in all three statements

> select count( case when b='T' then 1 else null) from test limit 50;

This is not a bug, this is your misunderstanding of what LIMIT does.

LIMIT applies to the output rows of the SELECT it's attached to,
not to the rows scanned to produce the output rows.  Since a select
count() will only have one output row, the LIMIT is irrelevant.

In 7.1 and later you can do something like

select count(...) from
(select * from test limit 50) as ss;

to apply the LIMIT before the aggregation step.

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-10-30 20:45:32
Subject: Re: Bug #504: multiple cursors cause transaction problems
Previous:From: Stephan SzaboDate: 2001-10-30 20:14:44
Subject: Re: Bug #503: case and LIMIT not working together

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group