List only unique users

Question

Table is users, columns are user_id, firstname, lastname, area.

The other table is user_sessions, and columns are user_id, logon, logoff.

To see who is logged on I use

select u.FIRSTNAME, u.LASTNAME, u.PHONE_ID, us.LOGON 
from USERS u 
    join USER_sessions us on u.USER_ID=us.user_id 
where cast (us.LOGON as date) = date 'now' 
and us.LOGOFF is null 
order by u.FIRSTNAME

The result is correct, but sometimes I get duplicated entries.

For example, same firstname and lastname, but different logon. I'd like to only see the latest logon.

Answer 1

It looks like field LOGON is kind of a timestamp type field; your question is not really precise, there.

So, I think, you want the maximum value of LOGON. For that, there is an aggregate function MAX() which has to be used with a GROUP BY.

select u.FIRSTNAME, u.LASTNAME, u.PHONE_ID, MAX(us.LOGON)
  from USERS u 
  join USER_sessions us
    on u.USER_ID = us.user_id 
  where cast (us.LOGON as date) = date 'now' 
    and us.LOGOFF is null
  group by u.FIRSTNAME, u.LASTNAME, u.PHONE_ID 
  order by u.FIRSTNAME

You'll get the latest LOGON for every occurence of u.FIRSTNAME, u.LASTNAME, u.PHONE_ID.

Note, that you can use current_date context variable instead of date 'now' cast.