SQL, last time using row_number () over the section

Question

I work with web click data, and just look for the last page_name with a visit to user_id (by timestamp). Using the code below, user_id is repeated and page_name is shown, with sorted downstream. However, I would like recent_click to always = 1. The request, when it is filled, will be used as a subquery in a larger request.

Here is my current code:

SELECT user_id, page_name, row_number() over(partition by session_id order by ts desc) as recent_click from clicks_data; user_id | page_name | recent_click --------+-------------+-------------- 0001 | login | 1 0001 | login | 2 0002 | home | 1 

Answer 1

You should transfer your request to a subquery and add where criteria:

 SELECT user_id, page_name, recent_click FROM ( SELECT user_id, page_name, row_number() over (partition by session_id order by ts desc) as recent_click from clicks_data ) T WHERE recent_click = 1 

Answer 2

You have to move the row_number() function to a subquery and then filter it in the outer query.
Something like that:

 SELECT * FROM ( SELECT [user_id] ,[page_name] ,ROW_NUMBER() OVER (PARTITION BY [session_id] ORDER BY [ts] DESC) AS [recent_click] FROM [clicks_data] )x WHERE [recent_click] = 1