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

Re: Special grouping on sorted data.

From: Nicolas Beuzeboc <nicolasb(at)norchemlab(dot)com>
To: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Special grouping on sorted data.
Date: 2008-09-23 17:59:34
Message-ID: 48D92E86.1080901@norchemlab.com (view raw or flat)
Thread:
Lists: pgsql-sql
Craig Ringer wrote:
>>
>> b    | n  |         stamp
>> ----------------------------------------
>> A    | 1  | 2008-09-20 06:07:47.981445 [1]
>> A    | 1  | 2008-09-20 06:08:13.294306 [1]
>> A    | 1  | 2008-09-20 06:12:02.046596 [1]
>> A    | 2  | 2008-09-20 06:12:26.267786 [2]
>> A    | 2  | 2008-09-20 06:12:47.750429 [2]
>> A    | 1  | 2008-09-20 06:13:12.152512 [3]
>> A    | 2  | 2008-09-20 06:13:39.052528 [4]
>> A    | 2  | 2008-09-20 06:14:12.875389 [4]
>>
>
> 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;

Nicolas



In response to

Responses

pgsql-sql by date

Next:From: Nicolas BeuzebocDate: 2008-09-23 18:12:20
Subject: Re: Special grouping on sorted data.
Previous:From: Osvaldo KussamaDate: 2008-09-23 15:57:31
Subject: Resp.: Multi-line text fields

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