1. The city temps table is a toy example, not meant to be realistic :-)
2. Yes, my (Java) algorithm is deterministic ... it will return
exactly one row per city, and that will be the row (or strictly, *a*
row) containing the highest temp. Temp value ties will break in favour
of earlier rows in Gui
On 24/02/10 22:47, Dave Crooke wrote:
I'd imagine it would be possible to have a query planner optimization
that would convert Garrett's DISTINCT ON syntax to do what I was
trying to, by realizing that DISTINCT ON X ... ORDER BY Y DESC is
going to return the the one row for each X which has the h
Dave Crooke wrote:
This is a generic SQL issue and not PG specific, but I'd like to get
an opinion from this list.
Consider the following data:
# \d bar
Table "public.bar"
Column |Type | Modifiers
+-+---
city
Garrett's is the best answer from the list the only fly in the
ointment here is that it performs a full sort of the records, which
isn't strictly necessary to the required output. This is functionally
equivalent to what I came up with for a MODE (most common value)
aggregation, but the syntax
I missed something:
select
B.City,
MaxCityTemp.Temp,
min(B.Date) as FirstMaxDate
from bar b
INNER JOIN (select city,max(temp) as Temp from Bar group by City) as
MaxCityTemp
ON B.City=MaxCityTemp.City AND B.Temp=MaxCityTemp.Temp
Group by
B.City,
You could do:
select
B.City,
MaxCityTemp.Temp,
min(B.Date) as FirstMaxDate
from bar b
INNER JOIN (select city,max(temp) as Temp from Bar group by City) as
MaxCityTemp
ON B.City=MaxCityTemp.City
Group by
B.City,
MaxCityTemp.Temp
Geo
This looks to be a perfect use for SELECT DISTINCT ON:
SELECT DISTINCT ON (city)
* FROM bar
ORDER BY city, temp desc
Or am I misunderstanding the issue?
Garrett Murphy
-Original Message-
From: pgsql-performance-ow...@postgresql.org
[mailto:pgsql-performance-ow...@postgresql.org] On Beh
Can you try using window functions?
Something like this:
select distinct
city,
first_value(temp) over w as max_temp,
first_value(date) over w as max_temp_date
from
cities
window w as (partition by city order by temp desc)
http://www.postgresql.org/docs/current/static/tutorial-window.h
This is a generic SQL issue and not PG specific, but I'd like to get
an opinion from this list.
Consider the following data:
# \d bar
Table "public.bar"
Column | Type | Modifiers
+-+---
city | character varying
On Wednesday 24 February 2010 07:55:36 A. Kretschmer wrote:
> In response to Kevin Kempter :
> > Hi All;
> >
> > I have a table that has daily partitions.
> >
> > The check constraints look like this:
> > CHECK (timezone('EST'::text, insert_dt) >= '2010-01-01'::date
> > AND timezone('EST'::text, in
In response to Kevin Kempter :
> Hi All;
>
> I have a table that has daily partitions.
>
> The check constraints look like this:
> CHECK (timezone('EST'::text, insert_dt) >= '2010-01-01'::date
> AND timezone('EST'::text, insert_dt) < '2010-01-02'::date)
>
> each partition has this index:
>
> -Original Message-
> From: negora [mailto:neg...@negora.com]
> Sent: Tuesday, February 23, 2010 4:33 PM
> To: Scott Carey
> Cc: Alvaro Herrera; pgsql-performance@postgresql.org
> Subject: Re: Internal operations when the planner makes a hash join.
>
> Thank you for explaining me the
Hi All;
I have a table that has daily partitions.
The check constraints look like this:
CHECK (timezone('EST'::text, insert_dt) >= '2010-01-01'::date
AND timezone('EST'::text, insert_dt) < '2010-01-02'::date)
each partition has this index:
"fact_idx1_20100101_on_cust_id" btree (cust_id)
I
It's always possible to rebuild into a consistent configuration by assigning
a precedence order; for parity RAID, the data drives take precedence over
parity drives, and for RAID-1 sets it assigns an arbitrary master.
You *should* never lose a whole stripe ... for example, RAID-5 updates do
"read
14 matches
Mail list logo
