pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelect the max on a field
4+ messages / 3 participants
[nested] [flat]
* Select the max on a field
@ 2002-09-12 13:09 Gaetano Mendola <mendola@bigfoot.com>
0 siblings, 2 replies; 4+ messages in thread
From: Gaetano Mendola @ 2002-09-12 13:09 UTC (permalink / raw)
To: pgsql-sql
Hi all,
Suppose that I have a table like this:
att_1 | att_2 | att_3 | att_4
--------------------------------
1 | a | y | y1
2 | b | y | y2
3 | a | xx | y3
4 | c | zz | y4
5 | a | t | y5
6 | c | x | y6
I want obtain all row that have for each att_2
the max of att_1
some like
SELECT *
FROM MY_TABLE
GROUP BY att_2
HAVING att_1 = max ( id_user_log)
for obtain:
att_1 | att_2 | att_3 | att_4
--------------------------------
2 | b | y | y2
5 | a | t | y5
6 | c | x | y6
Ciao
Gaetano
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Select the max on a field
@ 2002-09-12 13:15 Gaetano Mendola <mendola@bigfoot.com>
parent: Gaetano Mendola <mendola@bigfoot.com>
1 sibling, 1 reply; 4+ messages in thread
From: Gaetano Mendola @ 2002-09-12 13:15 UTC (permalink / raw)
To: pgsql-sql
"Gaetano Mendola" <mendola@bigfoot.com> wrote in message
news:alq3mr$2s7o$1@news.hub.org...
> Hi all,
>
> Suppose that I have a table like this:
>
>
> att_1 | att_2 | att_3 | att_4
> --------------------------------
> 1 | a | y | y1
> 2 | b | y | y2
> 3 | a | xx | y3
> 4 | c | zz | y4
> 5 | a | t | y5
> 6 | c | x | y6
>
>
>
> I want obtain all row that have for each att_2
> the max of att_1
I forgot to say: "with a single total query without sub select "
Ciao
Gaetano
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Select the max on a field
@ 2002-09-12 14:02 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Gaetano Mendola <mendola@bigfoot.com>
1 sibling, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2002-09-12 14:02 UTC (permalink / raw)
To: Gaetano Mendola <mendola@bigfoot.com>; +Cc: pgsql-sql
On Thu, 12 Sep 2002, Gaetano Mendola wrote:
> Hi all,
>
> Suppose that I have a table like this:
>
>
> att_1 | att_2 | att_3 | att_4
> --------------------------------
> 1 | a | y | y1
> 2 | b | y | y2
> 3 | a | xx | y3
> 4 | c | zz | y4
> 5 | a | t | y5
> 6 | c | x | y6
>
>
>
> I want obtain all row that have for each att_2
> the max of att_1
>
> some like
>
> SELECT *
> FROM MY_TABLE
> GROUP BY att_2
> HAVING att_1 = max ( id_user_log)
If you don't mind using postgres extensions and
don't need a particular att_1 ordering:
select distinct on (att_2) * from test
order by att_2, att_1 desc;
Otherwise I'm not sure you can do it without
a subselect somewhere.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Select the max on a field
@ 2002-09-12 14:03 Jeff Eckermann <jeff_eckermann@yahoo.com>
parent: Gaetano Mendola <mendola@bigfoot.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jeff Eckermann @ 2002-09-12 14:03 UTC (permalink / raw)
To: Gaetano Mendola <mendola@bigfoot.com>; pgsql-sql
--- Gaetano Mendola <mendola@bigfoot.com> wrote:
>
> "Gaetano Mendola" <mendola@bigfoot.com> wrote in
> message
> news:alq3mr$2s7o$1@news.hub.org...
> > Hi all,
> >
> > Suppose that I have a table like this:
> >
> >
> > att_1 | att_2 | att_3 | att_4
> > --------------------------------
> > 1 | a | y | y1
> > 2 | b | y | y2
> > 3 | a | xx | y3
> > 4 | c | zz | y4
> > 5 | a | t | y5
> > 6 | c | x | y6
> >
> >
> >
> > I want obtain all row that have for each att_2
> > the max of att_1
>
> I forgot to say: "with a single total query without
> sub select "
>
If you don't mind a non-portable feature, DISTINCT ON
should do what you want. Something like:
SELECT DISTINCT ON (att_2) att_1, att_2, att_3, att_4
FROM table
ORDER BY att_2, att_1 DESC;
__________________________________________________
Do you Yahoo!?
Yahoo! News - Today's headlines
http://news.yahoo.com
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-09-12 14:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-12 13:09 Select the max on a field Gaetano Mendola <mendola@bigfoot.com>
2002-09-12 13:15 ` Gaetano Mendola <mendola@bigfoot.com>
2002-09-12 14:03 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-09-12 14:02 ` Stephan Szabo <sszabo@megazone23.bigpanda.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