Fetch second activity in SQL

Question

1.00/5 (1 vote)

See more:

I have below table

TABLE=USER_ACTIVITY

|USERNAME|ACTIVITY|STARTDATE |ENDDATE   |
|--------|--------|----------|----------|
|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

Posted 1-May-23 5:30am

AsthanaP

Updated 1-May-23 23:01pm

OriginalGriff

v2

Add a Solution

Comments

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 :(

[no name] 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.

2 solutions

Add a Solution

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

CHill60 · Answer 1 · 2023-05-01T23:01:00

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

SQL

SELECT A.* 
from
(
     SELECT a.username,activity, ROW_NUMBER() over(partition by a.username order by startdate desc) seq
		,b.activities
	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

Maciej Los · Answer 2 · 2023-05-01T09:03:00

Solution 1

If you want to get second activity starting from the new one...

SQL

SELECT A.* 
from
(
SELECT username,activity, ROW_NUMBER() over(partion by username order by startdate desc) seq
FROM employee_Activity
) AS A
WHERE A.seq=2

Posted 1-May-23 9:03am

Maciej Los

Comments

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.

Fetch second activity in SQL

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Fetch second activity in SQL

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us