CODEWITHSUNDEEP
sql-serverwhere-clausehaving-clause
codebot
codebot

Reputation: 697

Combine WHERE and HAVING clause in a query

I have the following query

SELECT  analysis, start, stop
FROM table     
GROUP BY start    
WHERE userid = 12 AND playerid = 67     
HAVING CAST(start AS date) = MAX(CAST(start AS date))    
ORDER BY start ;

start and stop are type datetime, like 2016-10-23 00:00:00.000

we have a lot of them for the same day. I want to have all the start/stop in the same, last day.

So ,my logic is get all the start and stop datetimes of the max day (=today)

So if today is 2016-10-23 get all the start and stop that are

`2016-10-23 00:10:00.000`
`2016-10-23 00:20:00.000`
`2016-10-23 00:40:00.000`
`2016-10-23 00:80:00.000`

The error I get is incorrect syntax near the keyword WHERE

How can I fix this?

Thanks

Upvotes: 1

Views: 1062

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You want to select all rows the date of which is the maximum date in the table. One main query for the rows to show and a subquery in the where clause for the maximum day,

select *
from mytable
where cast(start as date) = (select max(cast(start as date)) from mytable);

Upvotes: 3

Related Questions