Click here to Skip to main content
15,789,776 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have below table


|Amy     |Travel  |2020-02-12|2020-02-20|
|Amy     |Dancing |2020-02-21|2020-02-23|
|Amy     |Karate  |2020-02-24|2020-02-28|
|Anna    |Skating |2020-01-12|2020-01-20|
|Anna    |Singing |2020-02-21|2020-04-23|
|Anna    |Travel  |2020-02-24|2020-05-28|
|Anna    |Piano   |2020-05-29|2020-06-20|
|Anna    |Painting|2020-06-21|2020-07-20|
|Joe     |Travel  |2020-02-11|2020-02-18|

My task is fetch latest second activity of each user.

Amy  -> Dancing,
Anna -> Piano,
Joe  -> Travel

but it is not showing record of joe as it is having only single row.

What I have tried:

select * from
(select username,activity,dense_rank() over(partion by username order by startdate desc) seq
from employee_Activity)
where seq=2
Updated 2-May-23 0:01am
0x01AA 1-May-23 11:43am    
Wild guess, something like this: COUNT(*) OVER (PARTITION BY xyz) xyzcount and then WHERE seq = 2 OR xyzcount = 1
AsthanaP 1-May-23 12:06pm    
it showing 1st and 2nd activity of all users.
where i want only 2nd activity of each user but if user having only 1 activity then that one activity should show
0x01AA 1-May-23 13:45pm    
You are right, I was wrong :(
Member 15627495 1-May-23 12:08pm    
in a well built DB, you only need, as table structure :

people_id | activity_id | start_datetime | end_datetime

but your question is not for this about.

A slight correction to Solution 1 - this query will get the 2nd activity if there are 2 or more activities for a user, OR the only activity if there is only one
     SELECT a.username,activity, ROW_NUMBER() over(partition by a.username order by startdate desc) seq
	FROM employee_Activity a
	join (
			SELECT username, COUNT(*) as activities from employee_Activity 
			group by USERNAME
		) b on a.USERNAME = b.USERNAME
) AS A
WHERE A.seq=2 or A.activities = 1
I've used a sub-query here but you could use a CTE, a temporary table or a table variable if you prefer
Share this answer
If you want to get second activity starting from the new one...
SELECT username,activity, ROW_NUMBER() over(partion by username order by startdate desc) seq
FROM employee_Activity
) AS A
WHERE A.seq=2
Share this answer
0x01AA 1-May-23 15:17pm    
But what if no second exists and there is only one?
Maciej Los 2-May-23 15:26pm    
Try it :)
0x01AA 2-May-23 16:32pm    
I tried it and it does not return the one for which only one entry exists ;)
Maciej Los 5-May-23 15:00pm    
So, it's working as expected :)
Sorry, Bruno, for the late reply. I'm a bit busy.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900