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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
