CODEWITHSUNDEEP
mysqlsqlgroup-by
djechlin
djechlin

Reputation: 60788

How to use variable in "having" clause without selecting it?

I have this large query here:

select
 user_contact_id as userContactId,
 max(attempt_number) as attemptNumber,
 max(sent_timestamp) as sentTimestamp,
 source as source,
 from
    share_email_tracking `set`
 group by
    user_contact_id
 having
     (attemptNumber = 1 and date_sub(CURDATE(), interval 4 day) >= date(sentTimestamp))

Problem is, I'm not actually interested in attemptNumber or sentTimestamp. I just need those to compute the "having" clause. I'm not aware of any syntax that does this, and I think this is a more general problem than "having" so I haven't been able to find it in documentation on it. I believe temp variables are possible but to my knowledge these are session-specific, not query-specific, and I don't want them polluting the state. Is this possible?

In real life I'm duplicating the sentTimestamp several times so I should avoid just substituting it into the having raw.

Upvotes: 2

Views: 1727

Answers (2)

Tom
Tom

Reputation: 6663

You could just put them in your Having. Just use the actual expression and not the alias.

select  user_contact_id as userContactId,
        source as source

from    share_email_tracking `set`

group by user_contact_id

having (max(attempt_number) = 1 and date_sub(CURDATE(), interval 4 day) >= max(sent_timestamp))

You could do this if you want to alias expressions so that you don't have to write them multiple times in your having clause. Create a sub-select and then use the aliases in the WHERE clause of your main query.

select  userContactId, source

FROM

(

 select  user_contact_id as userContactId,
         max(attempt_number) as attemptNumber,
         max(sent_timestamp) as sentTimestamp,
         source as source

 from    share_email_tracking `set`

 group by user_contact_id

) as x

WHERE    (attemptNumber = 1 and date_sub(CURDATE(), interval 4 day) >= date(sentTimestamp))

Upvotes: 3

echo_Me
echo_Me

Reputation: 37253

you could do them in where clause

   select
 user_contact_id as userContactId,
 max(attempt_number) as attemptNumber,
 max(sent_timestamp) as sentTimestamp,
 source as source,
from
   share_email_tracking `set`

group by
user_contact_id
HAVING  max(attempt_number) = 1  AND date(sentTimestamp) <= date_sub(CURDATE(), interval 4 day)

Upvotes: 0

Related Questions