pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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





view thread (4+ messages)

Message-ID: <36C2CB5E.BBE030AE@flex.ro>
Permalink:  ../36C2CB5E.BBE030AE@flex.ro/
Also on:    postgresql.org/message-id/36C2CB5E.BBE030AE@flex.ro

 · 

reply

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