• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

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
0
SunScreenCert
Asked:
SunScreenCert
5 Solutions
 
YZlatCommented:
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
0
 
David L. HansenProgrammer AnalystCommented:
SELECT user_id, activity_type
FROM myTable
WHERE activity_type = 2
GROUP BY user_id
0
 
sameer2010Commented:
Use this:
SELECT TOP 1 USER_ID
FROM YOUR_TABLE
WHERE ACTIVITY_TYPE=2
GROUP BY USER_ID
ORDER BY COUNT(*) DESC

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
roeibCommented:
select user_id, count(user_id)
from user_table
where
activity_type = 2
group by user_id
having count(user_id) =
( select max(cnt) from
  ( select user_id, count(user_id) cnt
    from user_table
   where
    activity_type = 2
    group by user_id
  )
);
0
 
YZlatCommented:
SELECT user_id
FROM Table1
GROUP BY user_id
HAVING COUNT(user_id)=
(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)
0
 
YZlatCommented:
the query above will give you the user_id of the user with the higher count of records where activity_count = 2

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)
0
 
ralmadaCommented:
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

Open in new window

0
 
ralmadaCommented:
Can you please advise why my suggestion didn't work for you?
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now