pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedConsecutive row count query
6+ messages / 5 participants
[nested] [flat]
* Consecutive row count query
@ 2005-03-17 20:21 Leon Stringer <leon.stringer@ntlworld.com>
2005-03-17 21:15 ` Re: Consecutive row count query Bruno Wolff III <bruno@wolff.to>
2005-03-17 21:32 ` Re: Consecutive row count query Jaime Casanova <systemguards@gmail.com>
2005-03-17 23:00 ` Re: Consecutive row count query Greg Stark <gsstark@mit.edu>
2005-03-17 23:35 ` Re: Consecutive row count query Andrew Hammond <ahammond@ca.afilias.info>
2005-03-18 21:07 ` Re: Consecutive row count query Leon Stringer <leon.stringer@ntlworld.com>
0 siblings, 5 replies; 6+ messages in thread
From: Leon Stringer @ 2005-03-17 20:21 UTC (permalink / raw)
To: pgsql-sql
Hi,
I wondered if anyone could answer the following question:
If I have a table such as the one below:
col1 col_order
-----------
Apple 1
Apple 2
Orange 3
Banana 4
Apple 5
Is there a way I can get the following results:
Apple 2
Orange 1
Banana 1
Apple 1
i.e. Each row is printed ordered by col_order but consecutive
appearances of the same col1 result in only a single line in the result
with the number of consecutive appearances.
Obviously I could store the table as:
col1 col_order col_count
--------------------------
Apple 1 2
Orange 2 1
Banana 3 1
Apple 4 1
But since (in my intended table) most rows will have col_count = 1, this
seems like unnecessary normalization (and semantically "wrong").
Thanks in advance for any help,
Leon Stringer
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Consecutive row count query
2005-03-17 20:21 Consecutive row count query Leon Stringer <leon.stringer@ntlworld.com>
@ 2005-03-17 21:15 ` Bruno Wolff III <bruno@wolff.to>
4 siblings, 0 replies; 6+ messages in thread
From: Bruno Wolff III @ 2005-03-17 21:15 UTC (permalink / raw)
To: Leon Stringer <leon.stringer@ntlworld.com>; +Cc: pgsql-sql
On Thu, Mar 17, 2005 at 20:21:24 +0000,
Leon Stringer <leon.stringer@ntlworld.com> wrote:
> Hi,
>
> I wondered if anyone could answer the following question:
>
> If I have a table such as the one below:
>
> col1 col_order
> -----------
> Apple 1
> Apple 2
> Orange 3
> Banana 4
> Apple 5
>
> Is there a way I can get the following results:
>
> Apple 2
> Orange 1
> Banana 1
> Apple 1
>
> i.e. Each row is printed ordered by col_order but consecutive
> appearances of the same col1 result in only a single line in the result
> with the number of consecutive appearances.
Which col_order value do you expect to use in ordering?
You can probably join a grouped version of the table to get the counts
with a distinct on version of the table to do what you want.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Consecutive row count query
2005-03-17 20:21 Consecutive row count query Leon Stringer <leon.stringer@ntlworld.com>
@ 2005-03-17 21:32 ` Jaime Casanova <systemguards@gmail.com>
4 siblings, 0 replies; 6+ messages in thread
From: Jaime Casanova @ 2005-03-17 21:32 UTC (permalink / raw)
To: Leon Stringer <leon.stringer@ntlworld.com>; +Cc: pgsql-sql
On Thu, 17 Mar 2005 20:21:24 +0000, Leon Stringer
<leon.stringer@ntlworld.com> wrote:
> Hi,
>
> I wondered if anyone could answer the following question:
>
> If I have a table such as the one below:
>
> col1 col_order
> -----------
> Apple 1
> Apple 2
> Orange 3
> Banana 4
> Apple 5
>
> Is there a way I can get the following results:
>
> Apple 2
> Orange 1
> Banana 1
> Apple 1
>
A function?
regards,
Jaime Casanova
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Consecutive row count query
2005-03-17 20:21 Consecutive row count query Leon Stringer <leon.stringer@ntlworld.com>
@ 2005-03-17 23:00 ` Greg Stark <gsstark@mit.edu>
4 siblings, 0 replies; 6+ messages in thread
From: Greg Stark @ 2005-03-17 23:00 UTC (permalink / raw)
To: Leon Stringer <leon.stringer@ntlworld.com>; +Cc: pgsql-sql
Leon Stringer <leon.stringer@ntlworld.com> writes:
> Hi,
>
> I wondered if anyone could answer the following question:
>
> If I have a table such as the one below:
>
> col1 col_order
> -----------
> Apple 1
> Apple 2
> Orange 3
> Banana 4
> Apple 5
>
> Is there a way I can get the following results:
>
> Apple 2
> Orange 1
> Banana 1
> Apple 1
Maybe. But not easily or efficiently.
How about this:
SELECT a.col1, a.col_order
FROM tab as a
LEFT OUTER JOIN tab as b
ON (b.col_order = a.col_order+1 AND b.col1=a.col1)
WHERE b.col1 IS NULL
> But since (in my intended table) most rows will have col_count = 1, this
> seems like unnecessary normalization (and semantically "wrong").
I think this looks like a better option. "unnecessary normalization" is an odd
phrase. Unless you can point at some reason that the denormalized seems *more*
convenient --and much *more* convenient at that-- not less convenient then you
should go for it.
Besides, that col_count column's only going to be four bytes. Unless the
"Apple" data is really short it'll only take a few col_count>1 to make it
worthwhile.
The only reason you might have a problem is if it's really "semantically
wrong" which would be if there's data attached to Apple or Orange that might
be different from one streak of results to the other.
--
greg
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Consecutive row count query
2005-03-17 20:21 Consecutive row count query Leon Stringer <leon.stringer@ntlworld.com>
@ 2005-03-17 23:35 ` Andrew Hammond <ahammond@ca.afilias.info>
4 siblings, 0 replies; 6+ messages in thread
From: Andrew Hammond @ 2005-03-17 23:35 UTC (permalink / raw)
To: Leon Stringer <leon.stringer@ntlworld.com>; +Cc: pgsql-sql
You could hack it using a custom aggregate. NB: you'll want to reset the
categorizer_seq every now and then. And this isn't safe for concurrent
queries. You could make it safe for concurrent queries by using a
complex type for STYPE, but I didn't bother. I also haven't debugged
this, but I think it expresses the concept.
CREATE SEQUENCE categorizer_seq;
CREATE OR REPLACE FUNCTION categorizer_func (string, string) RETURNS
bigint VOLATILE CALLED ON NULL INPUT AS '
SELECT CASE WHEN $1 = $2
THEN (SELECT last_value FROM categorizer_seq)
ELSE nextval(''categorizer_seq'')
END AS category
' LANGUAGE SQL;
CREATE AGGREGATE categorizer (
BASETYPE = text,
SFUNC = categorizer_func,
STYPE = text,
INITCOND = ''
);
SELECT col1, count(*)
FROM (
SELECT col1, cagetorizer(col1) AS category
FROM mytable
ORDER BY col_order
) tmp
GROUP BY (col1, category);
Leon Stringer wrote:
> Hi,
>
> I wondered if anyone could answer the following question:
>
> If I have a table such as the one below:
>
> col1 col_order
> -----------
> Apple 1
> Apple 2
> Orange 3
> Banana 4
> Apple 5
>
> Is there a way I can get the following results:
>
> Apple 2
> Orange 1
> Banana 1
> Apple 1
>
> i.e. Each row is printed ordered by col_order but consecutive
> appearances of the same col1 result in only a single line in the result
> with the number of consecutive appearances.
>
> Obviously I could store the table as:
>
> col1 col_order col_count
> --------------------------
> Apple 1 2
> Orange 2 1
> Banana 3 1
> Apple 4 1
>
> But since (in my intended table) most rows will have col_count = 1, this
> seems like unnecessary normalization (and semantically "wrong").
>
> Thanks in advance for any help,
>
> Leon Stringer
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Consecutive row count query
2005-03-17 20:21 Consecutive row count query Leon Stringer <leon.stringer@ntlworld.com>
@ 2005-03-18 21:07 ` Leon Stringer <leon.stringer@ntlworld.com>
4 siblings, 0 replies; 6+ messages in thread
From: Leon Stringer @ 2005-03-18 21:07 UTC (permalink / raw)
To: pgsql-sql
> I wondered if anyone could answer the following question:
Thanks for your responses, I think I'll just add the extra column in as
Greg suggests. (BTW: I mean "unnecessary *denormalization*" which I hope
is less odd!).
Leon...
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-03-18 21:07 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-17 20:21 Consecutive row count query Leon Stringer <leon.stringer@ntlworld.com>
2005-03-17 21:15 ` Bruno Wolff III <bruno@wolff.to>
2005-03-17 21:32 ` Jaime Casanova <systemguards@gmail.com>
2005-03-17 23:00 ` Greg Stark <gsstark@mit.edu>
2005-03-17 23:35 ` Andrew Hammond <ahammond@ca.afilias.info>
2005-03-18 21:07 ` Leon Stringer <leon.stringer@ntlworld.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox