This table contains all the login data of staff. I would like to report on only the latest by date i only want to show in this case: this is the data result of the below: I am only interested in 3/8/2021|3/8/2021|Korner|Altor|

select a.session_start, a.session_end, b.last_name, b.first_name
from use_log_with_transactions_view a
inner join staff_view b
  on a.staff_id = b.staff_id
order by b.last_name, b.first_name

CodePudding user response：

Since you want to have last, that would be maximum value for a session date, so you need to use max and group by:

select 
    max(a.session_start) as session_start, 
    max(a.session_end) as session_end, 
    b.last_name, 
    b.first_name 
from use_log_with_transactions_view a
inner join staff_view b
   on a.staff_id = b.staff_id
group by b.last_name, b.first_name
order by b.last_name, b.first_name

CodePudding user response：

If you want the latest session_start for all users.
Then you could use the ROW_NUMBER function.
If the version of your database supports it.

select 
 log.session_start, 
 log.session_end, 
 staff.last_name, 
 staff.first_name
from (
   select t.*,
   row_number() over (partition by staff_id order by session_start desc) as rn
   from use_log_with_transactions_view t
) log
join staff_view as staff
  on staff.staff_id = log.staff_id
where log.rn = 1

But for only 1 user it's simple.
If you order descending by session_start for the staff, then take the top 1.

In MS Sql Server you can select top 1 ...
In MySql you can use LIMIT 1

select 
 log.session_start, 
 log.session_end, 
 staff.last_name, 
 staff.first_name 
from use_log_with_transactions_view as log
join staff_view as staff
  on staff.staff_id = log.staff_id
where staff.last_name = 'Korner' 
  and staff.first_name = 'Altor' 
order by log.session_start desc
limit 1