Tuzki
Tuzki

Reputation: 491

SQL find last date and last time of event

I am trying to list the latest event (only one row output)

Basically the very last date of the event and the very last time of the event.

The below code doesn't work if it is max(starttime), but it works if it is min(starttime)

However, if I do min(starttime), it'll tell me the last date of the event but the first event of that last date.

I would like to get the result of the last event time of the last date. (therefore only 1 row output)

Any ideas?

SELECT * from Events
where dateheld in (select max(dateheld) from events)
AND starttime in (select max(starttime) from events)

Upvotes: 2

Views: 9639

Answers (2)

user1639464
user1639464

Reputation:

The issue seems to be the logic as your WHERE statement could be generating two disjoint sets (the max(time) of the table does not share a row with the max(date)). What about something such as the following: (DISTINCT ensures only one record)

SELECT MAX(dateheld) AS 'MaxDate', starttime 
FROM Events
WHERE starttime in 
      (SELECT MAX (starttime) AS temp FROM events)

Upvotes: 3

gangreen
gangreen

Reputation: 909

you can limit your result to the top 1 record returned:

select * from events
order by dateheld, starttime desc
limit 1

edit to meet new requirement

select max(starttime) as maxstart, dateheld
from events
where dateheld = (select max(dateheld) as maxdateheld from events)

Upvotes: 1

Related Questions