Re: [SQL] Select max field

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(at)flex(dot)ro
Braila, ROMANIA