From: George Moga <george@flex.ro>
To: Bob Kruger <bkruger@mindspring.com>
To: SQL PostgreSQL <pgsql-sql@postgreSQL.org>
Subject: Re: [SQL] Select max field
Date: Thu, 11 Feb 1999 14:21:50 +0200
Message-ID: <36C2CB5E.BBE030AE@flex.ro> (raw)
References: <3.0.5.32.19990210134244.007a8b10@mindspring.com>
Bob Kruger wrote:
> I am looking for a way to determine the largest value of a number of fields
> in a tuple.
>
> Example: In a table with the fields id_no, t1, t2 ,t3 ,t4 ,t5
> Select the id_no and the greatest value from fields t1, t2, t3,
> t4, t5.
>
> I have tried the following, but with no success:
>
> select id_no, max(t1, t2, t3, t4, t5) from table_1 ;
>
> Anyone have any suggestions?
>
> Thanks in advance for any assistance.
>
> Regards - Bob
If I undestund your problem try:
CREATE SEQUENCE abc;
CREATE TABLE max_col (id_no int4 DEFAULT NEXTVAL ( 'abc' ), t1 int4, t2 int4, t3
int4, t4 int4, t5 int4);
INSERT INTO max_col (t1, t2, t3, t4, t5) VALUES (1, 2, 3, 4, 5);
INSERT INTO max_col (t1, t2, t3, t4, t5) VALUES (11, 12, 31, 14, 55);
INSERT INTO max_col (t1, t2, t3, t4, t5) VALUES (13, 82, 13, 24, 65);
INSERT INTO max_col (t1, t2, t3, t4, t5) VALUES (31, 12, 73, 24, 45);
INSERT INTO max_col (t1, t2, t3, t4, t5) VALUES (31, 12, 63, 44, 75);
INSERT INTO max_col (t1, t2, t3, t4, t5) VALUES (51, 62, 53, 34, 25);
INSERT INTO max_col (t1, t2, t3, t4, t5) VALUES (61, 25, 33, 42, 53);
INSERT INTO max_col (t1, t2, t3, t4, t5) VALUES (15, 22, 35, 21, 45);
INSERT INTO max_col (t1, t2, t3, t4, t5) VALUES (13, 52, 31, 44, 56);
INSERT INTO max_col (t1, t2, t3, t4, t5) VALUES (51, 72, 23, 64, 55);
INSERT INTO max_col (t1, t2, t3, t4, t5) VALUES (61, 32, 23, 44, 25);
SELECT * FROM max_col;
id_no|t1|t2|t3|t4|t5
-----+--+--+--+--+--
1| 1| 2| 3| 4| 5
2|11|12|31|14|55
3|13|82|13|24|65
4|31|12|73|24|45
5|31|12|63|44|75
6|51|62|53|34|25
7|61|25|33|42|53
8|15|22|35|21|45
9|13|52|31|44|56
10|51|72|23|64|55
11|61|32|23|44|25
(11 rows)
create function max_val(int4) returns int4 as '
declare
maxval int4;
temp int4;
row record;
begin
select * into row from max_col where id_no = $1;
maxval:=row.t1;
if row.t2 > maxval then
maxval := row.t2;
end if;
if row.t3 > maxval then
maxval := row.t3;
end if;
if row.t4 > maxval then
maxval := row.t4;
end if;
if row.t5 > maxval then
maxval := row.t5;
end if;
return maxval;
end;
' language 'plpgsql';
SELECT *, max_val(id_no) AS "max. value" FROM max_col;
id_no|t1|t2|t3|t4|t5|max. value
-----+--+--+--+--+--+----------
1| 1| 2| 3| 4| 5| 5
2|11|12|31|14|55| 55
3|13|82|13|24|65| 82
4|31|12|73|24|45| 73
5|31|12|63|44|75| 75
6|51|62|53|34|25| 62
7|61|25|33|42|53| 61
8|15|22|35|21|45| 45
9|13|52|31|44|56| 56
10|51|72|23|64|55| 72
11|61|32|23|44|25| 61
(11 rows)
I use PostgreSQL 6.4 on Red Hat Linux 5.2 with 2.2.0 kernel version.
Sorry for my function (it's not what I like to be but ... it's all I can do now)
and for my english.
--
Best,
George Moga,
george@flex.ro
Braila, ROMANIA
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: george@flex.ro, bkruger@mindspring.com
Subject: Re: [SQL] Select max field
In-Reply-To: <36C2CB5E.BBE030AE@flex.ro>
References: <3.0.5.32.19990210134244.007a8b10@mindspring.com> <36C2CB5E.BBE030AE@flex.ro>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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