Max(Date) and having ... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Max(Date) and having ...

Author

Topic

annabanana
Starting Member

10 Posts

Posted - 2010-04-16 : 06:26:40

Hello !
I need to retrieve from a database all candidates whose last event_type is 'refused'. But I can't manage doing it.


 select MAX(DATE_EV) as LastDate, ID_CANDIDATE
 from EVENTS
 where (DELETED = 0)
and ID_CANDIDATE = 31237
group by ID_CANDIDATE
having EVENT_TYPE = 'Refused'

this having isn't accepted because EVENT_TYPE is not in the select nor in the group by. But I want the group by to only return the last event in time for each candidate...

how do you think I should do ?
thanks!!!!

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-04-16 : 06:49:07

Why You use group by and Having clause?

Just Where clause is enough!

select MAX(DATE_EV) as LastDate, ID_CANDIDATE
from EVENTS
where DELETED = 0 and ID_CANDIDATE = 31237 and EVENT_TYPE = 'Refused'

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-04-16 : 07:36:58

I think it will need a GROUP BY if there a COLUMN-expression (i.e. something that isn't just an Aggregate Function) in the SELECT (in particular the use of ID_CANDIDATE in this example), no?

Putting something in HAVING that could/should be in WHERE will just cause the SQL Query Plan optimiser to move it to the WHERE for you - so no harm done

However, better practice would be to only use HAVING for things that cannot be known at the WHERE stage - such as testing MAX(DATE_DV), or HAVING COUNT(*) > 1 and so on.

annabanana
Starting Member

10 Posts

Posted - 2010-04-16 : 07:40:59

that's the first thing I tried. But there's a trap here.
It won't work first because Id_candidate must be in an aggregation function.
If I delete ID_CANDIDATE, it will pass but won't give me the result I want anyway.
this


select MAX(DATE_EV) as LastDate, ID_CANDIDATE
from EVENTS
where DELETED = 0 and ID_CANDIDATE = 31237 and EVENT_TYPE = 'Refused'

will retrieve me the last event which type is refused. Whereas I want the last event WHEN it's a refused one.

let's say we have a candidate who had first an event_type like 'Interview' then 'refused' then 'Interview' again.

What I want here is the query to return nothing because the last event type concerning this candidate is not 'Refused'

If I do what you suggest, I will have one result, the next to last event. ...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-16 : 07:53:50

[code]select DATE_EV as LastDate, ID_CANDIDATE from
(
select
row_number() over (partition by ID_CANDIDATE order by DATE_EV DESC) as rownum,
DATE_EV,
ID_CANDIDATE,
EVENT_TYPE
from [EVENTS] where DELETED = 0
)dt
where rownum = 1
and EVENT_TYPE = 'Refused'
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

annabanana
Starting Member

10 Posts

Posted - 2010-04-16 : 08:47:59

this, is just perfect.
Thanks a lot.
I wasn't even aware of transact's possibilities..

quote:

Originally posted by webfred

select DATE_EV as LastDate, ID_CANDIDATE from
(
select
row_number() over (partition by ID_CANDIDATE order by DATE_EV DESC) as rownum,
DATE_EV,
ID_CANDIDATE,
EVENT_TYPE
from [EVENTS] where DELETED = 0
)dt
where rownum = 1
and EVENT_TYPE = 'Refused'

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-16 : 09:32:24

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources