List of only unique users
Table users
, columns user_id
, firstname
, lastname
, area
.
Another table user_sessions
, and columns - user_id
, logon
, logoff
.
To find out who is logged in 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 duplicate entries.
For example, the same firstname
and lastname
but different logins. I would like to see only the last entrance.
source to share
It looks like the field LOGON
is a timestamp type field; your question is not very accurate there.
So, I think you want the maximum value LOGON
. For this there is an aggregate function MAX()
that must be used with 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 will receive the last one LOGON
for each appearance u.FIRSTNAME, u.LASTNAME, u.PHONE_ID
.
Note that date 'now'
you can use a context variable instead of cast current_date
.
source to share