Re: [GENERAL] Select max field

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