SunScreenCert
asked on
SQL query
I have a table which has many records including user_id, activity_type
now i want to find the user_id which returns maximum records with activity_type = 2
now i want to find the user_id which returns maximum records with activity_type = 2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In Oracle I would do it like this
select * from
(
select user_id, count(*) as countactivity
from yourtable
where activity_type = 2
group by user_id
order by 2 desc
) a
where rownum = 1
Can you please advise why my suggestion didn't work for you?
also you can use this:
SELECT user_id
FROM Table1
GROUP BY user_id
HAVING COUNT(activity_count)=
(SELECT MAX(a.iCount)
FROM
(SELECT user_id, COUNT(user_id) as iCount
FROM Table1
WHERE activity_count=2
GROUP BY user_id) as a)