Thread: Select max field

Select max field

From
Bob Kruger
Date:

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


Re: [GENERAL] Select max field

From
Bob Dusek
Date:
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
>
>
>


Re: [GENERAL] Select max field

From
Bob Dusek
Date:
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
>
>
>



Re: [SQL] Select max field

From
George Moga
Date:
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