3

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
L_GIB
  • 125
  • 7

1 Answers1

2

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.

René Hoffmann
  • 2,766
  • 2
  • 20
  • 43
  • @LeoFazzi If my answer does fit your needs, you can mark it as accepted by clicking the checkmark on the left of it. – René Hoffmann May 03 '17 at 14:23
  • Thank you Rene'. I wonder if I could show totals on the bottom line. Probably union and run a count of? Never tried it... – L_GIB May 04 '17 at 07:58
  • @LeoFazzi This would be a new question and has to be asked as so. But you could have a look at [`row_count`](https://firebirdsql.org/refdocs/langrefupd25-row_count.html). – René Hoffmann May 04 '17 at 08:02
  • Cheers Rene'... will try it but not sure it will be :/ – L_GIB May 04 '17 at 08:25