Craig Ringer wrote:
>> b | n | stamp
>> A | 1 | 2008-09-20 06:07:47.981445 
>> A | 1 | 2008-09-20 06:08:13.294306 
>> A | 1 | 2008-09-20 06:12:02.046596 
>> A | 2 | 2008-09-20 06:12:26.267786 
>> A | 2 | 2008-09-20 06:12:47.750429 
>> A | 1 | 2008-09-20 06:13:12.152512 
>> A | 2 | 2008-09-20 06:13:39.052528 
>> A | 2 | 2008-09-20 06:14:12.875389 
> I'd be tempted to use a set-returning PL/PgSQL function to process an
> input set ordered by stamp and return a result whenever the (b,n) pair
> changed. I'm sure there's a cleverer set-oriented approach, but it's
> eluding me at present.
> You need a way to express the notion of "contiguous runs of (b,n)"
> which doesn't really exist in (set-oriented) SQL.
The numbers you have next to each row is exactly what I'm looking for.
You mention PL/PgSQL, I'm familiar with creating triggered procedures so
I'll look into that
> I suspect that Crystal Reports may be pulling the whole data set from
> PostgreSQL then doing its processing client-side.
Crystal report is running a simple pass through query that I wrote,
select b.n.stamp from table where stamp .... order by stamp
Then I use its grouping features, I group by b, then n but when I group
by n I don't specify ascending or descending order but "in original order"
And it ends up doing what I'm looking for.
I which distinct on was more flexible, it's not happy when the order by
set is different than the distinct on set.
I would like to be able to write select distinct on (b,n) b,n,stamp from
table where ... order by stamp;
In response to
pgsql-sql by date
|Next:||From: Nicolas Beuzeboc||Date: 2008-09-23 18:12:20|
|Subject: Re: Special grouping on sorted data.|
|Previous:||From: Osvaldo Kussama||Date: 2008-09-23 15:57:31|
|Subject: Resp.: Multi-line text fields|