From: | George Moga <george(at)flex(dot)ro> |
---|---|
To: | Bob Kruger <bkruger(at)mindspring(dot)com>, SQL PostgreSQL <pgsql-sql(at)postgreSQL(dot)org> |
Subject: | Re: [SQL] Select max field |
Date: | 1999-02-11 12:21:50 |
Message-ID: | 36C2CB5E.BBE030AE@flex.ro |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general 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(at)flex(dot)ro
Braila, ROMANIA
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Schoessow | 1999-02-11 14:48:14 | unsubscribe |
Previous Message | Blashko Alexander | 1999-02-11 10:45:08 | Problem with table,field names. |
From | Date | Subject | |
---|---|---|---|
Next Message | D'Arcy J.M. Cain | 1999-02-11 12:33:00 | Re: [SQL] RULE questions. |
Previous Message | Jan Wieck | 1999-02-11 12:21:41 | Re: [SQL] setting select limit? |