Re: [GENERAL] Select max field

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
>
>
>