How can I get just the latest row?

1

I have a query here which gives me the login times of staff. I would like it to just show me the latest most recent one. Here I can get the past 30 days but it is too many rows.
select distinct sv.staff_name, ul.session_start, ul.session_end, ul.ip_address

from use_log ul

inner join staff_view sv on ul.staff_id = sv.staff_id

where datediff(dd,ul.session_start, getdate()) <=30

and ul.staff_id is not null

2

always try to provide sample data. and also wrap your code with three of these tick marks `

 select * 
  from (
		 select *, 
		 ROW_NUMBER() OVER (
			  PARTITION BY sv.staff_id
			  ORDER BY ul.session_start desc
		   ) row_num
		from use_log ul
	   inner join staff_view sv on ul.staff_id = sv.staff_id
	  where datediff(dd,ul.session_start, getdate()) <=30
		and ul.staff_id is not null
) a
where row_num = 1
3

hi

hope this helps .. i don't about your data ..

; with cte as 
(
select 
  distinct   sv.staff_name
           , ul.session_start
		   , ul.session_end
		   , ul.ip_address
from 
  use_log ul
      inner join 
  staff_view sv on ul.staff_id = sv.staff_id
where 
    datediff(dd,ul.session_start, getdate()) <=30
and 
   ul.staff_id is not null
) 
select 
   top 1 * 
from 
  cte 
order by 
  session_start desc
4

Never use functions on a table column in a WHERE clause, because that can prevent index seeks.

Also, you don't need a check for "ul.staff_id is not null" since if it were null, the inner join from ul would fail and exclude that row automatically any way.

Edit: Add DESC to the ORDER BY, to correct the query:

;WITH cte_ul_sv AS (
select distinct sv.staff_name, ul.session_start, ul.session_end, ul.ip_address,
    row_number() over(partition by ul.staff_id order by ul.session_start DESC) as row_num
from dbo.use_log ul
inner join dbo.staff_view sv on ul.staff_id = sv.staff_id
where ul.session_start >= dateadd(day, -30, getdate()) --<<--<<--
)
select staff_name, session_start, session_end, ip_address
from cte_ul_sv
where row_num = 1
1 Like
5

Thank you Scott, the only issue here - do you see it as selecting the most recent date/time of the staff's login date/time?

6

OOPS, SORRY, NO. The sort should be DESCending, so add DESC to the ORDER BY:

;WITH cte_ul_sv AS (
select distinct sv.staff_name, ul.session_start, ul.session_end, ul.ip_address,
row_number() over(partition by ul.staff_id order by ul.session_start DESC) as row_num --<<--<<--
from dbo.use_log ul
inner join dbo.staff_view sv on ul.staff_id = sv.staff_id
where ul.session_start >= dateadd(day, -30, getdate())
)
select staff_name, session_start, session_end, ip_address
from cte_ul_sv
where row_num = 1

7

HI- How could I make the days as a param, for example, the user wants to see the last session in the past say 50 days.

8 
DECLARE @number_of_days int
SET @number_of_days = 50

;WITH cte_ul_sv AS (
select distinct sv.staff_name, ul.session_start, ul.session_end, ul.ip_address,
row_number() over(partition by ul.staff_id order by ul.session_start  **DESC** ) as row_num --<<--<<--
from dbo.use_log ul
inner join dbo.staff_view sv on ul.staff_id = sv.staff_id
where ul.session_start >= dateadd(day, @number_of_days, getdate())
)
select staff_name, session_start, session_end, ip_address
from cte_ul_sv
where row_num = 1