Thanks for the reply everyone.
In my case, it looks like there is no real drawback then, since what used
to happen is:
SELECT a,b,c
FROM table
WHERE clauses
OFFSET x LIMIT y;
followed by:
SELECT COUNT(*) FROM (
SELECT a,b,c
FROM table
WHERE clauses
);
(notice the lack of OFFSET and LIMIT)
Clemens Park wrote:
> Recently, during a performance improvement sweep for an application at
my company, one of the hotspots
> that was discovered was pagination.
>
> In order to display the correct pagination links on the page, the
pagination library we used (most
> pagination libraries for that
Thom, depesz, silly,
> SELECT DISTINCT ON (id_bf) id_bf, wert, letztespeicherung
> FROM rfmitzeit
> ORDER BY id_bf, letztespeicherung ASC;
yes, that does work. I put it in the real world query (my example was
reduced to the relevant parts), and it provides an impressive speedup
(down from 2234 to
2009/11/20 silly
> > and how would I use DISTINCT ON for this query? Please bear in mind,
> > that there is more then one id_bf (just stopped the sample data with
> > one of them)
>
> I posted the answer more than hour ago:
>
> SELECT DISTINCT ON (id_rf) * FROM rfmitzeit ORDER BY id_rf,
> let
> and how would I use DISTINCT ON for this query? Please bear in mind,
> that there is more then one id_bf (just stopped the sample data with
> one of them)
I posted the answer more than hour ago:
SELECT DISTINCT ON (id_rf) * FROM rfmitzeit ORDER BY id_rf,
letztespeicherung DESC;
The equivalent
In response to Massa, Harald Armin :
> > > Is it possible? How would the SQL utilizing WINDOW-functions look like?
> >
> > there is no point in using window functions in here - simply use
> > "DISTINCT ON".
>
> and how would I use DISTINCT ON for this query? Please bear in mind,
> that there is mo
2009/11/20 Massa, Harald Armin
> > > Is it possible? How would the SQL utilizing WINDOW-functions look like?
> >
> > there is no point in using window functions in here - simply use
> > "DISTINCT ON".
>
> and how would I use DISTINCT ON for this query? Please bear in mind,
> that there is more th
> > Is it possible? How would the SQL utilizing WINDOW-functions look like?
>
> there is no point in using window functions in here - simply use
> "DISTINCT ON".
and how would I use DISTINCT ON for this query? Please bear in mind,
that there is more then one id_bf (just stopped the sample data wit
In response to hubert depesz lubaczewski :
> On Fri, Nov 20, 2009 at 11:07:37AM +0100, Massa, Harald Armin wrote:
> > Is it possible? How would the SQL utilizing WINDOW-functions look like?
>
> there is no point in using window functions in here - simply use
> "DISTINCT ON".
Right, but he want to
Massa, Harald Armin, 20.11.2009 11:31:
no, that does not work:
"id_bf";"wert";"max"
98;"blue";"2009-11-10 00:00:00"
98;"red";"2009-11-10 00:00:00"
result is: I get the date of the youngest value.
My expected result is:
98;"red"
(that is, the entry of "wert" that is youngest)
Sorry then I mi
On Fri, Nov 20, 2009 at 11:07:37AM +0100, Massa, Harald Armin wrote:
> Is it possible? How would the SQL utilizing WINDOW-functions look like?
there is no point in using window functions in here - simply use
"DISTINCT ON".
Best regards,
depesz
--
Linkedin: http://www.linkedin.com/in/depesz /
oops, I forgot the partition by. Here's the correct query:
SELECT * FROM (SELECT *,row_number() OVER (PARTITION BY id_rf ORDER BY
letztespeicherung DESC) FROM rfmitzeit) t WHERE row_number=1
You can also do it using SELECT DISTINCT ON:
SELECT DISTINCT ON (id_rf) * FROM rfmitzeit ORDER BY id_rf,
SELECT * FROM (SELECT *,row_number() OVER (ORDER BY letztespeicherung
DESC) FROM rfmitzeit) t WHERE row_number=1
On Fri, Nov 20, 2009 at 5:31 AM, Massa, Harald Armin wrote:
> id_bf, wert, letztespeicherung:
>>>
>>> 98, 'blue', 2009-11-09
>>> 98, 'red', 2009-11-10
>>> now I have a
id_bf, wert, letztespeicherung:
> 98, 'blue', 2009-11-09
>> 98, 'red', 2009-11-10
>> now I have a select to get the "youngest value" for every id_bf:
>>
>
> Not tested:
>
> SELECT id_bf, wert,
> max(letztespeicherung) over (partition by id_bf)
> FROM rfmitzeit
>
no, that
Massa, Harald Armin, 20.11.2009 11:07:
I have a table
CREATE TABLE rfmitzeit
(
id_rf inet NOT NULL,
id_bf integer,
wert text,
letztespeicherung timestamp without time zone
CONSTRAINT repofeld_id_rf PRIMARY KEY (id_rf),
);
where for one id_bf there are stored mutliple values ("wert") at
hinking my next step would be to build from
source by grabbing the next daily snapshot. Is this the thing to do?
Regards,
Daniel
> To: mane...@hotmail.com
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] using window functions
> Date: Mon, 16 Mar 2009 23:58:25 -0400
Daniel Manesajian, 17.03.2009 04:33:
Hi,
I'm trying to get an advance taste of the window function feature that I
believe is supposed to be in 8.4. I'm running 8.4devel snapshot (dated
Jan-01 which seems kind of old) grabbed from the snapshot page on the
postgresql website.
When I try a si
Daniel Manesajian writes:
> When I try a simple query "select avg(my_int) over (order by my_int rows
> unbounded preceding) order by 1;" I get an error:
> ERROR: syntax error at or near "over"
You sure you're actually talking to the 8.4 server? Because that's
exactly what you'd get if you trie
18 matches
Mail list logo
