[SQL] Special grouping on sorted data.
Hi, I was looking for a simple solution to this problem. I can't find a way to group on b and n by just collapsing sequential n's (identical n's right next to each other) the sorting condition is the timestamp. 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 B| 1 | 2008-09-20 06:14:29.963352 B| 1 | 2008-09-20 06:14:52.247307 B| 3 | 2008-09-20 06:15:13.358151 B| 3 | 2008-09-20 06:15:44.307792 B| 3 | 2008-09-20 06:16:17.32131 B| 2 | 2008-09-20 06:16:44.030435 B| 2 | 2008-09-20 06:17:00.140907 C| 1 | 2008-09-20 06:17:50.067258 C| 1 | 2008-09-20 06:18:22.280218 C| 1 | 2008-09-20 06:18:41.661213 C| 1 | 2008-09-20 06:19:07.920327 C| 3 | 2008-09-20 06:19:26.166675 C| 2 | 2008-09-20 06:19:46.459439 C| 2 | 2008-09-20 06:20:04.634328 Here I give an example of the output I'm looking for, And I can find a way to do that in crystal report, but I would like postgresql to send it that way. If the next n is different create a new row. b| n |min_stamp | max_stamp - A| 1 | 2008-09-20 06:07:47.981445 | 2008-09-20 06:12:02.046596 A| 2 | 2008-09-20 06:12:26.267786 | 2008-09-20 06:12:47.750429 A| 1 | 2008-09-20 06:13:12.152512 | 2008-09-20 06:13:12.152512 A| 2 | 2008-09-20 06:13:39.052528 | 2008-09-20 06:14:12.875389 B| 1 | 2008-09-20 06:14:29.963352 | 2008-09-20 06:14:52.247307 B| 3 | 2008-09-20 06:15:13.358151 | 2008-09-20 06:16:17.32131 B| 2 | 2008-09-20 06:16:44.030435 | 2008-09-20 06:17:00.140907 C| 1 | 2008-09-20 06:17:50.067258 | 2008-09-20 06:19:07.920327 C| 3 | 2008-09-20 06:19:26.166675 | 2008-09-20 06:19:26.166675 C| 2 | 2008-09-20 06:19:46.459439 | 2008-09-20 06:20:04.634328 If I write a GROUP BY b,n there is going to be only two rows for b = 'A', if I use distinct on i get the same thing is there an easy way to do that ? Nicolas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Special grouping on sorted data.
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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Special grouping on sorted data.
I which distinct on ... I wish distinct on ... -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
