MIN function in a GROUP BY statement 2

[towerbase]

I have simplified my problem as follows:

Imagine a table containing details of play performances:

CREATE TABLE performances
( id INT PRIMARY KEY
, when DATE
, play VARCHAR(255)
, theatre VARCHAR(255)
)

The data might look like this

101 2001-03-01 Macbeth    Stratford
456 2002-04-27 Hamlet     London
438 1945-03-03 Macbeth    New York

I have been trying to write SQL which will return a list of first performances, theatre of first performance and the number of performances of each play as follows:

Hamlet   2002-04-27 London     1
Macbeth  1945-03-03 New York   2

My first effort

SELECT play, MIN(when), theatre, COUNT(*)
FROM performances
GROUP BY play

does not return the correct theatre (when done with the real table).

As a work around I am running a query to get a list of plays and then running a separate query for each play to find the MIN(when) and theatre. This takes a long time as there are thousands of plays!

Can anyone suggest how I can get the desired result with one SQL statement?

I am using MySQL v5.0.13

Andrew
Hampshire, UK

 

[r937]

select id 
     , when
     , play
     , theatre
     , ( select count(*)
           from performances
          where play = T.play ) as number_of_performances
  from performances as T
 where when =
       ( select min(when)
           from performances
          where play = T.play )

r937.com | rudy.ca

 

[towerbase]

Many thanks Rudy for that solution. My table actually contains 300,000 records and your SQL statements takes a long time (even with the play and when columns indexed).

However your solution has provided me with a better insight into writing SQL statements.

I'm always impressed with your knowledge and ability to write concise, clear SQL. Can you recommend a good text book?

Andrew
Hampshire, UK

 

[r937]

SQL for Smarties, third edition, by Joe Celko

thanks for the kind words

try a composite index on (play,when)



r937.com | rudy.ca

 

[feherke]

Hi

According to my experience, is a bad idea to put a sub-[tt]select[/tt] into the field part. Try to put it in the [tt]from[/tt] part, it should perform better.

[b]select[/b]
foo.*,p.theatre
[b]from[/b] (
  [b]select[/b]
  play,min(`when`) `when`,count(*) count
  [b]from[/b] performances
  [b]group by[/b] play
) foo
[b]inner join[/b] performances p [b]using[/b] (play,`when`)

Feherke.

http://rootshell.be/~feherke/

 

[towerbase]

feherke

Thank you very much for your solution. It seems to be much faster than Rudy's. So a "go faster" star for you!

I'll now try to understand your how SQL works.

Meanwhile, I've taken Rudy's advice and am waiting for Amazon to deliver SQL for Smarties.

Andrew
Hampshire, UK