pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedgroup by complications
4+ messages / 3 participants
[nested] [flat]
* group by complications
@ 2006-02-14 00:14 Mark Fenbers <Mark.Fenbers@noaa.gov>
0 siblings, 1 reply; 4+ messages in thread
From: Mark Fenbers @ 2006-02-14 00:14 UTC (permalink / raw)
To: pgsql-sql
select l.lid,l.fs,max(h.obstime) from location as l
inner join height as h on h.lid = l.lid
where l.fs > 0.0
group by l.lid,l.fs;
The above query works as expected in that is fetches the lid, fs and
time of the latest observation in the height table (for the
corresponding lid), but I also want to fetch (i.e., add to the select
list) the corresponding reading (h.obsvalue) which occurs at
max(h.obstime). I'm having trouble formulating the correct SQL syntax
to pull out the l.lid, l.fs, and the most recent h.obvalue (with or
without the time that it occurred).
Logistically, I want to do something like this:
select l.lid,l.fs,most_recent(h.obsvalue) from location as l
inner join height as h on h.lid = l.lid
where l.fs > 0.0
group by l.lid,l.fs;
Can someone offer hints, please?
Mark
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: group by complications
@ 2006-02-14 02:29 chester c young <chestercyoung@yahoo.com>
parent: Mark Fenbers <Mark.Fenbers@noaa.gov>
0 siblings, 1 reply; 4+ messages in thread
From: chester c young @ 2006-02-14 02:29 UTC (permalink / raw)
To: Mark Fenbers <Mark.Fenbers@noaa.gov>; pgsql-sql
--- Mark Fenbers <Mark.Fenbers@noaa.gov> wrote:
> select l.lid,l.fs,max(h.obstime) from location as l
> inner join height as h on h.lid = l.lid
> where l.fs > 0.0
> group by l.lid,l.fs;
>
> The above query works as expected in that is fetches the lid, fs and
> time of the latest observation in the height table (for the
> corresponding lid), but I also want to fetch (i.e., add to the select
> list) the corresponding reading (h.obsvalue) which occurs at
> max(h.obstime). I'm having trouble formulating the correct SQL
> syntax
> to pull out the l.lid, l.fs, and the most recent h.obvalue (with or
> without the time that it occurred).
>
> Logistically, I want to do something like this:
>
> select l.lid,l.fs,most_recent(h.obsvalue) from location as l
> inner join height as h on h.lid = l.lid
> where l.fs > 0.0
> group by l.lid,l.fs;
>
use your original query as part of the from clause, then add columns to
it through a subquery or a join. try something like this:
select q1.*,
(select obsvalue from height where lid=q1.lid and obstime=q1.obstime)
as obsvalue
from
(select l.lid,l.fs,max(h.obstime) as obstime1 from location as l
inner join height as h on h.lid = l.lid
where l.fs > 0.0
group by l.lid,l.fs ) q1;
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: group by complications
@ 2006-02-16 17:47 Mark Fenbers <Mark.Fenbers@noaa.gov>
parent: chester c young <chestercyoung@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Mark Fenbers @ 2006-02-16 17:47 UTC (permalink / raw)
To: chester c young <chestercyoung@yahoo.com>; +Cc: pgsql-sql
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Wow! I didn't know you could have a (select ...) as a replacement for
a 'from' table/query. Your SQL worked as-is, except I had to add a
'limit 1' to the first subquery.<br>
<br>
Thanks! I would have never figured that out on my own!<br>
<br>
Mark<br>
<br>
chester c young wrote:
<blockquote cite="mid20060214022937.77875.qmail@web54305.mail.yahoo.com"
type="cite">
<pre wrap="">--- Mark Fenbers <a class="moz-txt-link-rfc2396E" href="mailto:Mark.Fenbers@noaa.gov"><Mark.Fenbers@noaa.gov></a> wrote:
</pre>
<blockquote type="cite">
<pre wrap="">select l.lid,l.fs,max(h.obstime) from location as l
inner join height as h on h.lid = l.lid
where l.fs > 0.0
group by l.lid,l.fs;
The above query works as expected in that is fetches the lid, fs and
time of the latest observation in the height table (for the
corresponding lid), but I also want to fetch (i.e., add to the select
list) the corresponding reading (h.obsvalue) which occurs at
max(h.obstime). I'm having trouble formulating the correct SQL
syntax
to pull out the l.lid, l.fs, and the most recent h.obvalue (with or
without the time that it occurred).
Logistically, I want to do something like this:
select l.lid,l.fs,most_recent(h.obsvalue) from location as l
inner join height as h on h.lid = l.lid
where l.fs > 0.0
group by l.lid,l.fs;
</pre>
</blockquote>
<pre wrap=""><!---->
use your original query as part of the from clause, then add columns to
it through a subquery or a join. try something like this:
select q1.*,
(select obsvalue from height where lid=q1.lid and obstime=q1.obstime)
as obsvalue
from
(select l.lid,l.fs,max(h.obstime) as obstime1 from location as l
inner join height as h on h.lid = l.lid
where l.fs > 0.0
group by l.lid,l.fs ) q1;
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
<a class="moz-txt-link-freetext" href="http://mail.yahoo.com">http://mail.yahoo.com</a;
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
</pre>
</blockquote>
</body>
</html>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: group by complications
@ 2006-02-16 22:41 Markus Schaber <schabi@logix-tt.com>
parent: Mark Fenbers <Mark.Fenbers@noaa.gov>
0 siblings, 0 replies; 4+ messages in thread
From: Markus Schaber @ 2006-02-16 22:41 UTC (permalink / raw)
To: Mark Fenbers <Mark.Fenbers@noaa.gov>; +Cc: pgsql-sql
Hi, Mark,
Mark Fenbers schrieb:
> Wow! I didn't know you could have a (select ...) as a replacement for a
> 'from' table/query. Your SQL worked as-is, except I had to add a 'limit
> 1' to the first subquery.
>
> Thanks! I would have never figured that out on my own!
SQL has more power than most think, and PostgreSQL does a good job in
implementing most of it. Much more than some other well known "free"
databases.
Have a look at http://www.postgresql.org/docs/8.1/static/sql-select.html
and the other SQL commands at
http://www.postgresql.org/docs/8.1/static/sql-commands.html (or your
local copy of the manual).
HTH,
Markus
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-02-16 22:41 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-14 00:14 group by complications Mark Fenbers <Mark.Fenbers@noaa.gov>
2006-02-14 02:29 ` chester c young <chestercyoung@yahoo.com>
2006-02-16 17:47 ` Mark Fenbers <Mark.Fenbers@noaa.gov>
2006-02-16 22:41 ` Markus Schaber <schabi@logix-tt.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