Picking a row from each group

#11

On Mon, 3 May 2010, Thomas Kellerer wrote:

Quote:

Tom Anderson wrote on 03.05.2010 15:14:

Firstly, how can i ask for:

the latest temperature from each station

SELECT station_number, temperature
FROM weather_reports wr
WHERE wr.report_date = (SELECT max(report_date)
FROM weather_reports w2
WHERE w2.station_number = wr.station_number)

Cool. This is a correlated subquery, right? On reading up on these, i find
an essentially identical example to yours:

http://searchoracle.techtarget.com/a...lated-subquery

Quote:

and some operator which takes the first row in each sorted group. I
don't know what that last operator is, and i don't know how to put them
all together.

This is called windowing functions, and the corresponding function would be
first_value()

Sorry to ask such a basic question; if there's any fine manual i could
read online that would let me work this out myself, please don't
hesitate to tell me to read it.

http://www.postgresql.org/docs/curre...al-window.html

Windowing functions are defined in the SQL standard and supported by all
major DBMS The above tutorial will also work for other DBMS that comply
with the standard

Oh, this is very cool. I need to have a play with these.

tom

--
There is no latest trend.

#12

On Mon, 3 May 2010, Robert Klemme wrote:

Quote:

On 03.05.2010 15:14, Tom Anderson wrote:

If i was doing this in imperative code, the former would look like:

for each station:
sort all the reports by date
take the latest

But i don't know how to transpose this to SQL. I'm guessing that
involves some combination of GROUP BY station_number, to give the aspect
of working over all the stations, ORDER BY report_date, to give the
aspect of looking at the data in terms of time, and some operator which
takes the first row in each sorted group. I don't know what that last
operator is, and i don't know how to put them all together.

You need GROUP BY but you do not need ORDER BY - rather you need MAX.

That is exactly the key thing i was missing. It's part of a very different
way of thinking of things to what i'm used to.

tom

--
There is no latest trend.

#13

On 05/04/2010 04:54 AM, Ben Finney wrote:

Quote:

You would do well to read C.J. Date's “Introduction to Database Systems”
URL:http://en.wikipedia.org/wiki/Special:BookSources/0321197844>.

I also dare to suggest, after that, the third edition of "Joe Celko's
SQL for smarties" - I found it gentler towards the naive readers, and
less 'taliban' (more practical) than other books from him or Date.

As for Date's, even in the latest "SQL and relational theory", with his
utter denial of NULL and the like, I think he's teaching me to program
in a SQL variant that does not exists in the wild. The day Postgres
supports Tutorial-D, I'll be the first to have a picture of him in my
wallet.

#14

On Fri, 7 May 2010, Marco Mariani wrote:

Quote:

On 05/04/2010 04:54 AM, Ben Finney wrote:

You would do well to read C.J. Date's ?Introduction to Database Systems?
URL:http://en.wikipedia.org/wiki/Special:BookSources/0321197844>.

I also dare to suggest, after that, the third edition of "Joe Celko's
SQL for smarties" - I found it gentler towards the naive readers, and
less 'taliban' (more practical) than other books from him or Date.

Seems to be online here, which is nice:

http://books.google.dk/books?id=Hi9fMnOoRtAC

Quote:

As for Date's, even in the latest "SQL and relational theory", with his
utter denial of NULL and the like, I think he's teaching me to program
in a SQL variant that does not exists in the wild. The day Postgres
supports Tutorial-D, I'll be the first to have a picture of him in my
wallet.

I read Introduction to Database Systems years ago and really enjoyed it
(feels a bit weird to be saying that about a book on database theory, but
i did!). It's a great foundation - and i'm glad i had that rather than
some Learn SQL in 21 Days rubbish - but Mr Celko's book sounds more like
the kind of thing i want to read right now.

tom

--
Eccentric? Of course. If you want to avoid a problem which afflicts most
people there's no escape from eccentricity. -- Chris Malcolm

#15

On 05/07/2010 07:33 AM, Tom Anderson wrote:

Quote:

On Mon, 3 May 2010, Robert Klemme wrote:

On 03.05.2010 15:14, Tom Anderson wrote:

If i was doing this in imperative code, the former would look like:

for each station:
sort all the reports by date
take the latest

But i don't know how to transpose this to SQL. I'm guessing that
involves some combination of GROUP BY station_number, to give the aspect
of working over all the stations, ORDER BY report_date, to give the
aspect of looking at the data in terms of time, and some operator which
takes the first row in each sorted group. I don't know what that last
operator is, and i don't know how to put them all together.

You need GROUP BY but you do not need ORDER BY - rather you need MAX.

That is exactly the key thing i was missing. It's part of a very
different way of thinking of things to what i'm used to.

Well, you "only" need to think in sets rather than procedural. ;-) SQL
is a descriptive language and not an imperative language although there
are features (like functions) that can make it look imperative at times.
I believe the important core concepts to grasp are

1. description of sets (i.e what belongs into a set, filtering)

2. combination of sets via joins (i.e. association of a tuple from one
set with those from another set)

Of course there's more to SQL but if you understand these two concepts
you have a pretty good handle on the matter.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Picking a row from each group

comp.databases comp.databases

Re: Picking a row from each group - 05-07-2010 , 12:32 AM

Re: Picking a row from each group - 05-07-2010 , 12:33 AM

Re: Picking a row from each group - 05-07-2010 , 04:30 AM

Re: Picking a row from each group - 05-07-2010 , 04:36 AM

Re: Picking a row from each group - 05-09-2010 , 04:36 AM