pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelect max field
4+ messages / 3 participants
[nested] [flat]
* Select max field
@ 1999-02-10 19:42 Bob Kruger <bkruger@mindspring.com>
1999-02-11 00:29 ` Re: [GENERAL] Select max field Bob Dusek <bobd@palaver.net>
1999-02-11 08:32 ` Re: [GENERAL] Select max field Bob Dusek <bobd@palaver.net>
1999-02-11 12:21 ` Re: [SQL] Select max field George Moga <george@flex.ro>
0 siblings, 3 replies; 4+ messages in thread
From: Bob Kruger @ 1999-02-10 19:42 UTC (permalink / raw)
To: pgsql-sql; pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [GENERAL] Select max field
1999-02-10 19:42 Select max field Bob Kruger <bkruger@mindspring.com>
@ 1999-02-11 00:29 ` Bob Dusek <bobd@palaver.net>
2 siblings, 0 replies; 4+ messages in thread
From: Bob Dusek @ 1999-02-11 00:29 UTC (permalink / raw)
To: Bob Kruger <bkruger@mindspring.com>; +Cc: pgsql-sql; pgsql-general@postgreSQL.org
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
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [GENERAL] Select max field
1999-02-10 19:42 Select max field Bob Kruger <bkruger@mindspring.com>
@ 1999-02-11 08:32 ` Bob Dusek <bobd@palaver.net>
2 siblings, 0 replies; 4+ messages in thread
From: Bob Dusek @ 1999-02-11 08:32 UTC (permalink / raw)
To: Bob Kruger <bkruger@mindspring.com>; +Cc: pgsql-sql; pgsql-general@postgreSQL.org
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
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] Select max field
1999-02-10 19:42 Select max field Bob Kruger <bkruger@mindspring.com>
@ 1999-02-11 12:21 ` George Moga <george@flex.ro>
2 siblings, 0 replies; 4+ messages in thread
From: George Moga @ 1999-02-11 12:21 UTC (permalink / raw)
To: Bob Kruger <bkruger@mindspring.com>; 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@flex.ro
Braila, ROMANIA
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1999-02-11 12:21 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-02-10 19:42 Select max field Bob Kruger <bkruger@mindspring.com>
1999-02-11 00:29 ` Bob Dusek <bobd@palaver.net>
1999-02-11 08:32 ` Bob Dusek <bobd@palaver.net>
1999-02-11 12:21 ` George Moga <george@flex.ro>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox