CODEWITHSUNDEEP
sqlms-access
PetGriffin
PetGriffin

Reputation: 545

Confused about JOINs with sub-query

I have database table P which looks something like this:

date        wellID  rate
01/01/1988  well01  300
01/02/1988  well01  301
01/03/1988  well01  310
........
01/04/1988  well02  500
01/05/1988  well02  490
01/06/1988  well02  495
........
01/04/1988  well03  700
01/05/1988  well03  750
01/06/1988  well03  700
........   
01/07/1989  well04  400
01/08/1989  well04  350
01/09/1989  well04  340
........

I want to extract initial rate for each well ie the date of the earliest record for each well along with the associated rate. I am expecting something like this:

date        wellID  oil
01/01/1988  well01  300
01/04/1988  well02  500
01/04/1988  well03  700
01/07/1989  well04  400

I am trying to extract this data from Access using the following SQL query 

SELECT p.date, p.wellID, p.oil
FROM p 
LEFT JOIN
(
   SELECT wellID, min(date) AS MinDate
   FROM p
) AS a 
ON p.wellID = a.wellID
WHERE p.date = a.MinDate;

but Access gives this message "You tried to execute a query that does not include the specified expression 'WellId'as part of an aggregate function". I'm afraid this is beyond my limited knowledge of SQL so all help appreciated

Thanks!

Upvotes: 0

Views: 45

Answers (2)

barnard
barnard

Reputation: 71

You left out a group by clause on the inner aggregate query.

Try this:

SELECT p.date, p.wellID, p.oil
FROM p 
LEFT JOIN
(
   SELECT wellID, min(date) AS MinDate
   FROM p
   GROUP BY wellID
) AS a 
ON p.wellID = a.wellID
WHERE p.date = a.MinDate;

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

add group by, and you probably want INNER JOIN for this case

INNER JOIN
(
   SELECT wellID, min(date) AS MinDate
   FROM p
   GROUP BY wellID
) AS a

Upvotes: 2

Related Questions