Re: [SQL] Select max field - Mailing list pgsql-sql

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