SQL nested select, simple issue

Question

I'm trying to retrieve the event ID and endDate from a list that includes multiple endDates for the same ID. That step was easy enough using max() and grouping, but where I am struggling is then restricting those results to only those where the max end date has already passed.

I believe this requires the use of nested SQL, but I just can't get it to work.

Records:

eventID  | endDate
   1         05/01/2015
   1         05/01/2014
   1         05/01/2013
   2         05/01/2016
   3         07/01/2016
   4         05/01/2014
   4         05/01/2013

Desired results (where today = June 2015):

eventID  | endDate
   1         05/01/2015
   4         05/01/2014

This code gets me the list of all events and their max end date, and from this I need to get the ContentID and EndDate where the EndDate is before today. I've tried adding a where statement to limit the date but that gets executed prior to the max(event.EndDate) which doesn't get me the results I need.

 select event.ContentID, max(event.EndDate) as EndDate
 from event
 group by event.ContentID

Any help would be much appreciated!

Which server do you use? sql-server? Oracle? MySQL?

Ionic
– Ionic

2015-06-22 19:38:22 +00:00
Commented Jun 22, 2015 at 19:38 — Ionic
– Ionic, Commented Jun 22, 2015 at 19:38

Greg Viers · Accepted Answer · 2015-06-22 19:38:27Z

6

Use the having clause instead of where to filter based on aggregate formulas such as max():

SELECT event.ContentID
    ,max(event.EndDate) AS EndDate
FROM event
GROUP BY event.ContentID
HAVING max(event.EndDate) < getdate()

answered Jun 22, 2015 at 19:38

Greg Viers

3,5233 gold badges22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ionic · Accepted Answer · 2015-06-22 19:39:24Z

3

On SQL-Server you could use this:

SELECT event.ContentID, max(event.EndDate) as EndDate
FROM event
GROUP BY event.ContentID
HAVING MAX(event.EndDate) <= GETDATE()

on MySQL:

SELECT event.ContentID, max(event.EndDate) as EndDate
FROM event
GROUP BY event.ContentID
HAVING MAX(event.EndDate) <= CURDATE()

answered Jun 22, 2015 at 19:39

Ionic

3,9451 gold badge14 silver badges33 bronze badges

Comments

Nickknack · Accepted Answer · 2015-06-22 19:42:37Z

0

You can use the first SQL as the from clause of the outer SQL, since the first SQL creates a table. So you can do:

SELECT 
   contentID,
   EndDate
FROM
(
   SELECT
      event.ContentID AS ContentID,
      max(event.EndDate) AS EndDate
   FROM
      event
   GROUP BY
      event.contentID
)
WHERE
   MONTH(event.EndDate) = 6 --where the enddate has a month of June (6)

Note: I wasn't able to test this exact code, but the idea should answer your question.

answered Jun 22, 2015 at 19:42

Nickknack

8674 gold badges13 silver badges26 bronze badges

1 Comment

Nickknack Over a year ago

The other answers are more optimal; go with those.

Geoff Garcia · Accepted Answer · 2015-06-22 19:58:51Z

0

I came up with the following which seems to do the trick, but not sure this is the best approach?

 select content.ContentID, content.Title, b.EndDate
 from content
 inner join(
    select event.ContentID, max(event.EndDate) as EndDate
    from event
    group by event.ContentID
    ) as b on b.ContentID=content.ContentID
where b.EndDate< getdate()

answered Jun 22, 2015 at 19:58

Geoff Garcia

297 bronze badges

1 Comment

MatBailie Over a year ago

Why would you use this rather than just using a HAVING clause?

Collectives™ on Stack Overflow

SQL nested select, simple issue

4 Answers 4

Comments

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related