[SQL] Get max value from an comma separated string
Hi, I've a field that contain values-comma-separated like A) 1;2;3;;5 -- ;2;;4;5 but also B) 12;34;18 how I can get the max value? For A I tried: SELECT max(array_upper(string_to_array(answer,';'),1)) FROM values; and work fine, but for B case I don't find a solution like SELECT max(string_to_array(answer,';')) FROM values; Any ideas? Thanks for any hint Chiacchiera con i tuoi amici in tempo reale! http://it.yahoo.com/mail_it/foot/*http://it.messenger.yahoo.com ---(end of broadcast)--- TIP 1: 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
[SQL] distinct aggregate with complex type dont see the equality operator
Dear List, i want to built an aggregate function wich should summing up values in a distinct manner: dsum(DISTINCT ROW(value, distinction)). I implemented all things i thought i need but it dont work. i get always an 'could not identify an equality operator for type dsum_type'-error nevertheless the operator will be identified outside the aggregate. Here is the code for the equality operator: CREATE OR REPLACE FUNCTION dsum_type_greater(dsum_type, dsum_type) RETURNS boolean AS $f$ SELECT $1.distinction > $2.distinction; $f$ LANGUAGE sql IMMUTABLE STRICT; CREATE OPERATOR = ( PROCEDURE = dsum_type_equality, LEFTARG = dsum_type, RIGHTARG = dsum_type, COMMUTATOR = = ); And this happens: -- result is FALSE SELECT ROW(5, 'foo')::dsum_type = ROW(5, 'bar')::dsum_type; -- result is TRUE SELECT ROW(5, 'foo')::dsum_type = ROW(5, 'foo')::dsum_type; -- result is TRUE SELECT ROW(4, 'foo')::dsum_type = ROW(5, 'foo')::dsum_type; -- works for me SELECT dsum(ROW(wert, name)::dsum_type) FROM table1; -- ERROR: could not identify an equality operator for type dsum_type SELECT dsum(DISTINCT ROW(wert, name)::dsum_type) FROM table1; i added the less then and greater then ops too, but it still wont work. What i am doing wrong or can i achieve the wished result without usinge a comlex type? regards, Thomas! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] distinct aggregate with complex type dont see the equality operator [solved]
using DISTINCT with complex types depends on an OPERATOR CLASS for
B-Tree-Indexes i found out. the error msg 'could not identify an
equality operator for type' was confusing.
i post this complete example. maybe someone else is running in this problem too.
regards,
thomas!
CREATE TYPE named_value AS (
value_name text,
value numeric
);
CREATE OR REPLACE FUNCTION named_value_lt(named_value, named_value)
RETURNS boolean AS $f$
SELECT $1.value_name < $2.value_name;
$f$ LANGUAGE sql IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION named_value_lt_eq(named_value, named_value)
RETURNS boolean AS $f$
SELECT $1.value_name <= $2.value_name;
$f$ LANGUAGE sql IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION named_value_eq(named_value, named_value)
RETURNS boolean AS $f$
SELECT $1.value_name = $2.value_name;
$f$ LANGUAGE sql IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION named_value_gt_eq(named_value, named_value)
RETURNS boolean AS $f$
SELECT $1.value_name >= $2.value_name;
$f$ LANGUAGE sql IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION named_value_gt(named_value, named_value)
RETURNS boolean AS $f$
SELECT $1.value_name > $2.value_name;
$f$ LANGUAGE sql IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION named_value_cmp(named_value, named_value)
RETURNS integer AS $f$
SELECT CASE
WHEN $1.value_name < $2.value_name THEN -1
WHEN $1.value_name = $2.value_name THEN 0
ELSE 1
END;
$f$ LANGUAGE sql IMMUTABLE STRICT;
CREATE OPERATOR < (
PROCEDURE = named_value_lt,
LEFTARG = named_value,
RIGHTARG = named_value,
COMMUTATOR = >
);
CREATE OPERATOR <= (
PROCEDURE = named_value_lt_eq,
LEFTARG = named_value,
RIGHTARG = named_value,
COMMUTATOR = >=
);
CREATE OPERATOR = (
PROCEDURE = named_value_eq,
LEFTARG = named_value,
RIGHTARG = named_value,
COMMUTATOR = =
);
CREATE OPERATOR >= (
PROCEDURE = named_value_gt_eq,
LEFTARG = named_value,
RIGHTARG = named_value,
COMMUTATOR = <=
);
CREATE OPERATOR > (
PROCEDURE = named_value_gt,
LEFTARG = named_value,
RIGHTARG = named_value,
COMMUTATOR = <
);
CREATE OPERATOR CLASS named_value_ops
DEFAULT FOR TYPE named_value USING btree AS
OPERATOR1< ,
OPERATOR2<= ,
OPERATOR3= ,
OPERATOR4>= ,
OPERATOR5> ,
FUNCTION1named_value_cmp(named_value, named_value);
CREATE OR REPLACE FUNCTION sum_final(named_value)
RETURNS numeric AS $f$
SELECT $1.value;
$f$ LANGUAGE sql IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION sum_accum(named_value, named_value)
RETURNS named_value AS $f$
SELECT ROW('', $1.value + $2.value)::named_value;
$f$ LANGUAGE sql IMMUTABLE STRICT;
CREATE AGGREGATE sum(
BASETYPE = named_value,
SFUNC = sum_accum,
STYPE = named_value,
FINALFUNC = sum_final,
INITCOND = "('',0)"
);
-- result is FALSE
SELECT ROW('foo', 5)::named_value = ROW('bar', 5)::named_value;
-- result is TRUE
SELECT ROW('foo', 5)::named_value = ROW('foo', 5)::named_value;
-- result is TRUE
SELECT ROW('foo', 4)::named_value = ROW('foo', 5)::named_value;
-- works for me
SELECT sum(ROW(name, wert)::named_value) FROM table1;
-- now works for me too
SELECT DISTINCT ROW(wert, name)::named_value FROM table1;
SELECT sum(DISTINCT ROW(name, wert)::named_value) FROM table1;
---(end of broadcast)---
TIP 1: 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
Re: [SQL] Am I crazy or is this SQL not possible
"Collin Peters" wrote: > DISTINCT also doesn't work > > SELECT DISTINCT ON (email_broadcast_id) * > FROM email_broadcast_history > ORDER BY date_sent > > As you have to have the DISTINCT fields matching the ORDER BY fields. > I have to ORDER BY date_sent, but I can't do a DISTINCT on date_sent DISTINCT does work. You don't have to do DISTINCT on date_sent. The left part of the ORDER BY must match the DISTINCT expression(s): SELECT DISTINCT ON(broadcast_id) broadcast_id, date_sent, status FROM table ORDER BY broadcast_id, date_sent DESC; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Am I crazy or is this SQL not possible
This reminds me of an interview question: I was asked how to get a maximum column from a table without using max. How would you do that? Select my_column from my_table order by my_column desc limit 1 -- == Aaron Bono PresidentAranya Software Technologies, Inc. http://www.aranya.com We take care of your technology needs. Phone: (816) 695-6071 == ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Am I crazy or is this SQL not possible
I think this approach will only work if each broadcast_id has the same maximum date_sent value. You really need to do the group by in a sub-query to bring the broadcast_id together with the max date_sent. On 6/1/06, Yasir Malik <[EMAIL PROTECTED]> wrote: > What I would like to do is simply get the last date_sent and it's > status for every broadcast. I can't do a GROUP BY because I can't put > an aggregate on the status column. > > SELECT MAX(date_sent), status > FROM broadcast_history > GROUP BY broadcast_id > You could try the following: select status from broadcast_history bh where bh.date_sent = (select max(bh2.date_sent) from broadcast_history bh2); == Aaron Bono PresidentAranya Software Technologies, Inc. http://www.aranya.com We take care of your technology needs. Phone: (816) 695-6071 == ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Fwd: Stalled post to pgsql-sql
I think this approach will only work if each broadcast_id has the same maximum date_sent value. You really need to do the group by in a sub-query to bring the broadcast_id together with the max date_sent. -Aaron Bono On 6/1/06, Yasir Malik <[EMAIL PROTECTED]> wrote: > What I would like to do is simply get the last date_sent and it's > status for every broadcast. I can't do a GROUP BY because I can't put > an aggregate on the status column. > > SELECT MAX(date_sent), status > FROM broadcast_history > GROUP BY broadcast_id > You could try the following: select status from broadcast_history bh where bh.date_sent = (select max(bh2.date_sent) from broadcast_history bh2); ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] datestyle on windows environment: how to set?
Hello, I want to set the datestyle = 'ISO, European' for all my databases. I put that configuration into postgresql.conf file, but I didn't succeed. After rebooting all dates are like that: -MM-DD I'd like to have DD-MM- for my timestamp field. I have the latest version of pgsql and perl. Hereafter is an extract of my postgresql.conf file. Thanks and best regards, Filippo # - Locale and Formatting - datestyle = 'ISO, European' #timezone = unknown # actually, defaults to TZ # environment setting #australian_timezones = off #extra_float_digits = 0 # min -15, max 2 #client_encoding = sql_ascii# actually, defaults to database # encoding # These settings are initialized by initdb -- they might be changed lc_messages = 'C' # locale for system error message # strings lc_monetary = 'C' # locale for monetary formatting lc_numeric = 'C'# locale for number formatting lc_time = 'C' # locale for time formatting ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] datestyle on windows environment: how to set?
"filippo" <[EMAIL PROTECTED]> writes: > I'd like to have > DD-MM- for my timestamp field. The closest display format to that is 'SQL, DMY': regression=# set datestyle = 'sql, dmy'; SET regression=# select current_date; date 03/06/2006 (1 row) See the discussion of datestyle values in http://www.postgresql.org/docs/8.1/static/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
