pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGet the max(value1, value2, value3) from a table
11+ messages / 5 participants
[nested] [flat]
* Get the max(value1, value2, value3) from a table
@ 2008-01-07 22:03 Emi Lu <emilu@encs.concordia.ca>
2008-01-07 22:16 ` Re: Get the max(value1, value2, value3) from a table Scott Marlowe <scott.marlowe@gmail.com>
2008-01-07 22:38 ` Re: Get the max(value1, value2, value3) from a table Josh Williams <joshwilliams@ij.net>
0 siblings, 2 replies; 11+ messages in thread
From: Emi Lu @ 2008-01-07 22:03 UTC (permalink / raw)
To: pgsql-sql
Greetings,
Version: PostgreSQL 8.0.13 on i686-pc-linux-gnu
I have a table test(col1, col2, col3)
For each row, I'd like to get the "max"(col1, col2, col3).
For example, test(1, 5, 2)
test(8, 1, 3)
test(12, 1, 1)
select ?max?(col1, col2, col3) as result;
will return
result
-------
5
8
12
(3 rows)
Thanks!
Ly.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Get the max(value1, value2, value3) from a table
2008-01-07 22:03 Get the max(value1, value2, value3) from a table Emi Lu <emilu@encs.concordia.ca>
@ 2008-01-07 22:16 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-01-07 22:27 ` Re: Get the max(value1, value2, value3) from a table Richard Broersma Jr <rabroersma@yahoo.com>
1 sibling, 1 reply; 11+ messages in thread
From: Scott Marlowe @ 2008-01-07 22:16 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: pgsql-sql
On Jan 7, 2008 4:03 PM, Emi Lu <emilu@encs.concordia.ca> wrote:
> Greetings,
>
> Version: PostgreSQL 8.0.13 on i686-pc-linux-gnu
>
> I have a table test(col1, col2, col3)
>
> For each row, I'd like to get the "max"(col1, col2, col3).
>
> For example, test(1, 5, 2)
> test(8, 1, 3)
> test(12, 1, 1)
>
>
> select ?max?(col1, col2, col3) as result;
> will return
>
> result
> -------
> 5
> 8
> 12
select max(col1) from table
union all
select max(col2) from table
union all
select max(col3) from table
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Get the max(value1, value2, value3) from a table
2008-01-07 22:03 Get the max(value1, value2, value3) from a table Emi Lu <emilu@encs.concordia.ca>
2008-01-07 22:16 ` Re: Get the max(value1, value2, value3) from a table Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-01-07 22:27 ` Richard Broersma Jr <rabroersma@yahoo.com>
2008-01-07 22:37 ` Re: Get the max(value1, value2, value3) from a table Scott Marlowe <scott.marlowe@gmail.com>
2008-01-07 22:37 ` Re: Get the max(value1, value2, value3) from a table Emi Lu <emilu@encs.concordia.ca>
0 siblings, 2 replies; 11+ messages in thread
From: Richard Broersma Jr @ 2008-01-07 22:27 UTC (permalink / raw)
To: emilu@encs.concordia.ca, Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-sql
--- On Mon, 1/7/08, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> select max(col1) from table
> union all
> select max(col2) from table
> union all
> select max(col3) from table
Would the following work also?
SELECT MAX( GREATEST( col1, col2, col3 ) )
FROM TABLE;
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Get the max(value1, value2, value3) from a table
2008-01-07 22:03 Get the max(value1, value2, value3) from a table Emi Lu <emilu@encs.concordia.ca>
2008-01-07 22:16 ` Re: Get the max(value1, value2, value3) from a table Scott Marlowe <scott.marlowe@gmail.com>
2008-01-07 22:27 ` Re: Get the max(value1, value2, value3) from a table Richard Broersma Jr <rabroersma@yahoo.com>
@ 2008-01-07 22:37 ` Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Scott Marlowe @ 2008-01-07 22:37 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: emilu@encs.concordia.ca; pgsql-sql
On Jan 7, 2008 4:27 PM, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> --- On Mon, 1/7/08, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>
> > select max(col1) from table
> > union all
> > select max(col2) from table
> > union all
> > select max(col3) from table
>
> Would the following work also?
>
> SELECT MAX( GREATEST( col1, col2, col3 ) )
> FROM TABLE;
Not given his example output. Given that output, he wants the max of
each column, and your example would only return a single value.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Get the max(value1, value2, value3) from a table
2008-01-07 22:03 Get the max(value1, value2, value3) from a table Emi Lu <emilu@encs.concordia.ca>
2008-01-07 22:16 ` Re: Get the max(value1, value2, value3) from a table Scott Marlowe <scott.marlowe@gmail.com>
2008-01-07 22:27 ` Re: Get the max(value1, value2, value3) from a table Richard Broersma Jr <rabroersma@yahoo.com>
@ 2008-01-07 22:37 ` Emi Lu <emilu@encs.concordia.ca>
2008-01-07 22:40 ` Re: Get the max(value1, value2, value3) from a table Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Emi Lu @ 2008-01-07 22:37 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-sql
>
>> select max(col1) from table
>> union all
>> select max(col2) from table
>> union all
>> select max(col3) from table
No, this is not what I prefer; it makes complicate query.
>
> Would the following work also?
>
> SELECT MAX( GREATEST( col1, col2, col3 ) )
> FROM TABLE;
I would prefer this func. Unfortunately, the current version I have
8.02(http://www.postgresql.org/docs/8.0/static/functions-conditional.html)
does not support this func I am afraid :(
Thanks !
Ly.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Get the max(value1, value2, value3) from a table
2008-01-07 22:03 Get the max(value1, value2, value3) from a table Emi Lu <emilu@encs.concordia.ca>
2008-01-07 22:16 ` Re: Get the max(value1, value2, value3) from a table Scott Marlowe <scott.marlowe@gmail.com>
2008-01-07 22:27 ` Re: Get the max(value1, value2, value3) from a table Richard Broersma Jr <rabroersma@yahoo.com>
2008-01-07 22:37 ` Re: Get the max(value1, value2, value3) from a table Emi Lu <emilu@encs.concordia.ca>
@ 2008-01-07 22:40 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Scott Marlowe @ 2008-01-07 22:40 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: Richard Broersma Jr <rabroersma@yahoo.com>; pgsql-sql
On Jan 7, 2008 4:37 PM, Emi Lu <emilu@encs.concordia.ca> wrote:
>
> >
> >> select max(col1) from table
> >> union all
> >> select max(col2) from table
> >> union all
> >> select max(col3) from table
> No, this is not what I prefer; it makes complicate query.
Generally speaking when you have to make complicated queries to get
simple answers, then it's likely you have a normalization issue. IF
col1, col2, col3 were all a single column in an external table the
answer would likely fall out a little simpler.
But honestly, that's not a real complex query. PostgreSQL handles far
more complex queries for me everyday with hardly a murmer..
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Get the max(value1, value2, value3) from a table
2008-01-07 22:03 Get the max(value1, value2, value3) from a table Emi Lu <emilu@encs.concordia.ca>
@ 2008-01-07 22:38 ` Josh Williams <joshwilliams@ij.net>
2008-01-07 22:43 ` Re: Get the max(value1, value2, value3) from a table Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Josh Williams @ 2008-01-07 22:38 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: pgsql-sql
On Mon, 2008-01-07 at 17:03 -0500, Emi Lu wrote:
> select ?max?(col1, col2, col3) as result;
> will return
>
> result
> -------
> 5
> 8
> 12
>
> (3 rows)
8.1 (I believe?) introduced GREATEST(), which does precisely what you're
looking for.
But if 8.0 is a must, you'll probably have to create your own function
to do that. Which should be fairly easy to do if you're working with a
static number of columns/data types/etc...
- Josh
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Get the max(value1, value2, value3) from a table
2008-01-07 22:03 Get the max(value1, value2, value3) from a table Emi Lu <emilu@encs.concordia.ca>
2008-01-07 22:38 ` Re: Get the max(value1, value2, value3) from a table Josh Williams <joshwilliams@ij.net>
@ 2008-01-07 22:43 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-01-07 22:53 ` Re: Get the max(value1, value2, value3) from a table Emi Lu <emilu@encs.concordia.ca>
2008-01-07 23:16 ` Re: Get the max(value1, value2, value3) from a table Erik Jones <erik@myemma.com>
0 siblings, 2 replies; 11+ messages in thread
From: Scott Marlowe @ 2008-01-07 22:43 UTC (permalink / raw)
To: Josh Williams <joshwilliams@ij.net>; +Cc: emilu@encs.concordia.ca; pgsql-sql
On Jan 7, 2008 4:38 PM, Josh Williams <joshwilliams@ij.net> wrote:
> On Mon, 2008-01-07 at 17:03 -0500, Emi Lu wrote:
> > select ?max?(col1, col2, col3) as result;
> > will return
> >
> > result
> > -------
> > 5
> > 8
> > 12
> >
> > (3 rows)
>
> 8.1 (I believe?) introduced GREATEST(), which does precisely what you're
> looking for.
How would greatest give him three rows like that? Maybe I'm
misunderstanding what the OP was asking for...
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Get the max(value1, value2, value3) from a table
2008-01-07 22:03 Get the max(value1, value2, value3) from a table Emi Lu <emilu@encs.concordia.ca>
2008-01-07 22:38 ` Re: Get the max(value1, value2, value3) from a table Josh Williams <joshwilliams@ij.net>
2008-01-07 22:43 ` Re: Get the max(value1, value2, value3) from a table Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-01-07 22:53 ` Emi Lu <emilu@encs.concordia.ca>
2008-01-07 23:16 ` Re: Get the max(value1, value2, value3) from a table Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Emi Lu @ 2008-01-07 22:53 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Josh Williams <joshwilliams@ij.net>; pgsql-sql
>>> select ?max?(col1, col2, col3) as result;
>>> will return
>>>
>>> result
>>> -------
>>> 5
>>> 8
>>> 12
>>>
>>> (3 rows)
>> 8.1 (I believe?) introduced GREATEST(), which does precisely what you're
>> looking for.
>
> How would greatest give him three rows like that? Maybe I'm
> misunderstanding what the OP was asking for...
IF 8.1, "select greatest(col1, col2, col3) from test" is exactly what I
am looking for.
I would do the optional query by union/or for now.
Thanks!
Ly.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Get the max(value1, value2, value3) from a table
2008-01-07 22:03 Get the max(value1, value2, value3) from a table Emi Lu <emilu@encs.concordia.ca>
2008-01-07 22:38 ` Re: Get the max(value1, value2, value3) from a table Josh Williams <joshwilliams@ij.net>
2008-01-07 22:43 ` Re: Get the max(value1, value2, value3) from a table Scott Marlowe <scott.marlowe@gmail.com>
2008-01-07 22:53 ` Re: Get the max(value1, value2, value3) from a table Emi Lu <emilu@encs.concordia.ca>
@ 2008-01-07 23:16 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Scott Marlowe @ 2008-01-07 23:16 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: Josh Williams <joshwilliams@ij.net>; pgsql-sql
On Jan 7, 2008 4:53 PM, Emi Lu <emilu@encs.concordia.ca> wrote:
> >>> select ?max?(col1, col2, col3) as result;
> >>> will return
> >>>
> >>> result
> >>> -------
> >>> 5
> >>> 8
> >>> 12
> >>>
> >>> (3 rows)
> >> 8.1 (I believe?) introduced GREATEST(), which does precisely what you're
> >> looking for.
> >
> > How would greatest give him three rows like that? Maybe I'm
> > misunderstanding what the OP was asking for...
>
> IF 8.1, "select greatest(col1, col2, col3) from test" is exactly what I
> am looking for.
>
> I would do the optional query by union/or for now.
OK, looking back at your example, I do think I got it wrong. The
greatest thing should work... Here's a test from 8.1 to prove it ...
create table test (col1 int, col2 int, col3 int);
insert into test values (1,5,2);
smarlowe=# insert into test values (8,1,3);
smarlowe=# insert into test values (12,1,1);
select greatest(col1,col2,col3) from test;
greatest
----------
5
8
12
tada! So yeah, you want 8.1 (or 8.2 or 8.3)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Get the max(value1, value2, value3) from a table
2008-01-07 22:03 Get the max(value1, value2, value3) from a table Emi Lu <emilu@encs.concordia.ca>
2008-01-07 22:38 ` Re: Get the max(value1, value2, value3) from a table Josh Williams <joshwilliams@ij.net>
2008-01-07 22:43 ` Re: Get the max(value1, value2, value3) from a table Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-01-07 23:16 ` Erik Jones <erik@myemma.com>
1 sibling, 0 replies; 11+ messages in thread
From: Erik Jones @ 2008-01-07 23:16 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Josh Williams <joshwilliams@ij.net>; emilu@encs.concordia.ca; pgsql-sql
On Jan 7, 2008, at 4:43 PM, Scott Marlowe wrote:
> On Jan 7, 2008 4:38 PM, Josh Williams <joshwilliams@ij.net> wrote:
>> On Mon, 2008-01-07 at 17:03 -0500, Emi Lu wrote:
>>> select ?max?(col1, col2, col3) as result;
>>> will return
>>>
>>> result
>>> -------
>>> 5
>>> 8
>>> 12
>>>
>>> (3 rows)
>>
>> 8.1 (I believe?) introduced GREATEST(), which does precisely what
>> you're
>> looking for.
>
> How would greatest give him three rows like that? Maybe I'm
> misunderstanding what the OP was asking for...
His test data was:
col1 col2 col3
----------------------
1 5 2
8 1 3
12 1 1
So, SELECT test(col1, col2, col3);
returning
test
-----
5
8
12
Is giving the max of the three columns for each row.
Erik Jones
DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2008-01-07 23:16 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-07 22:03 Get the max(value1, value2, value3) from a table Emi Lu <emilu@encs.concordia.ca>
2008-01-07 22:16 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-01-07 22:27 ` Richard Broersma Jr <rabroersma@yahoo.com>
2008-01-07 22:37 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-01-07 22:37 ` Emi Lu <emilu@encs.concordia.ca>
2008-01-07 22:40 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-01-07 22:38 ` Josh Williams <joshwilliams@ij.net>
2008-01-07 22:43 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-01-07 22:53 ` Emi Lu <emilu@encs.concordia.ca>
2008-01-07 23:16 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-01-07 23:16 ` Erik Jones <erik@myemma.com>
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