0

I am trying to return the last changed time for a list of users, but i would like to return a row even if the a last changed time does not exist

select  user_id, 
        last_changed_time = MAX(ISNULL(change_date_time,0))     
from audit_log_tx
where user_id in (users) 
group by user_id
order by user_id asc 
3
  • 1
    user_id in (users) is not valid SQL. (unless that is the name of a column in which case the semantics are probably wrong) What is users representing there? Oct 15 '14 at 22:11
  • except for the syntax error Martin Smith already mentioned, the syntax looks valid. Can you share some sample data returing the "wrong" results from your point of view?
    – Lmu92
    Oct 15 '14 at 22:16
  • Sorry- I inserted (users) as a place holder for my list of users that im using in my query. Oct 17 '14 at 14:25
2
SELECT u.user_id, 
  column_name = COALESCE(MAX(change_date_time), '19000101')
  FROM dbo.users AS u
  LEFT OUTER JOIN dbo.audit_log_tx AS o
  ON u.user_id = o.user_id
  GROUP BY u.user_id;

Though I really don't like the use of token dates for missing data (especially 0).

0
-1

use any sql function below

IFNULL(change_date_time,0) or COALESCE(change_date_time,0) or ISNULL(change_date_time,0). http://www.w3schools.com/sql/sql_isnull.asp [use this link for more details][1]

if users is a tablename this is a clear syntax error. use

select  user_id, 
    last_changed_time = MAX(ISNULL(change_date_time,0))     
from audit_log_tx
where user_id in (select distinct user_id from users) 
group by user_id
order by user_id asc 
1
  • 1
    SQL-Server does not have an IFNULL function (the site you link is full of such wrong info.) And your query is basically the same as the OP's. It will not return users without a related row in the audit table. Oct 16 '14 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.