Thread: Select max field
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
Hey Bob, > 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 ; What do the fields t1, t2, t3, t4, and t5 represent? Do they all represent very different "real world" things, but are merely of the same same type (like int4 or something)? This seems to be a "style" issue to me. I would suggest creating a table that contains only the fields: id_no, tval, tnum. Then, for each id_no that you are tracking, you would have 5 entries in the table. For example's sake, let's say id_no = 33; t1 = 10; t2 = 20; t3 = 30; t4 = 40; t5 = 50.... Then, for id_no 33, your table would look like this: id_no | tval | tnum ------------------------- 33 | 10 | 1 33 | 20 | 2 33 | 30 | 3 33 | 40 | 4 33 | 50 | 5 Then, to select the max for id_no 33 you would perform the following query: select id_no, max(tval), tnum from tablename where id_no = 33 group by id_no; The query would return: id_no | max | tnum ------------------------ 33 | 50 | 5 To select the max for all id_no's you would simply drop the "where id_no = 33" clause from your query. I haven't tested this, yet. But, I'm pretty sure it would work. HTH, Bob > > Anyone have any suggestions? > > Thanks in advance for any assistance. > > Regards - Bob > > >
hey Bob et al,
I guess I'm going to have to eat a bit of crow, here.
The query I gave you was wrong, it won't work. Here are a couple that will,
for the database example that I gave you:
This one gives you id_no and max(tval) pairs:
select id_no, max(tval) from tablename group by id_no;
This one gives you the id_no, tval, tnum set for the max(tval) of the entire table:
select id_no, tval, tnum from tablename where tval = max(tval);
(kind of a worthless query for your purposes, or so it seems)
Sorry for the misleading email, earlier.
Bob
> 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 ;
What do the fields t1, t2, t3, t4, and t5 represent?
Do they all represent very different "real world" things, but are merely of the same
same type (like int4 or something)?
This seems to be a "style" issue to me. I would suggest creating a table that
contains only the fields: id_no, tval, tnum.
Then, for each id_no that you are tracking, you would have 5 entries in the
table. For example's sake, let's say id_no = 33; t1 = 10; t2 = 20; t3 = 30;
t4 = 40; t5 = 50.... Then, for id_no 33, your table would look like this:
id_no | tval | tnum
-------------------------
33 | 10 | 1
33 | 20 | 2
33 | 30 | 3
33 | 40 | 4
33 | 50 | 5
Then, to select the max for id_no 33 you would perform the following query:
select id_no, max(tval), tnum from tablename where id_no = 33 group by id_no;
The query would return:
id_no | max | tnum
------------------------
33 | 50 | 5
To select the max for all id_no's you would simply drop the "where id_no = 33"
clause from your query.
I haven't tested this, yet. But, I'm pretty sure it would work.
HTH,
Bob
>
> Anyone have any suggestions?
>
> Thanks in advance for any assistance.
>
> Regards - Bob
>
>
>
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