DISTINCT & MAX & GROUP BY Query
-
Hello,
Hopefully someone would be able to help me with this query. I have the following data -
ID - User ID - Task ID
100 - 1 - 1
101 - 1 - 2
102 - 1 - 3
103 - 1 - 4
104 - 1 - 5
105 - 1 - 5
106 - 1 - 6
107 - 1 - 7
108 - 1 - 7
109 - 2 - 1
110 - 2 - 1
111 - 2 - 2
111 - 2 - 2
112 - 2 - 3
113 - 2 - 4
114 - 2 - 5
115 - 2 - 6
116 - 2 - 7
117 - 2 - 8
118 - 2 - 9
119 - 2 - 12
120 - 3 - 1
121 - 3 - 2
122 - 3 - 2
123 - 4 - 1
124 - 4 - 2
125 - 4 - 3
Basically im trying to find a list of User IDs that have a DISTINCT MAX Task ID - eg
User ID 2 (there is only 1 maximum Task ID that is 12)
User ID 4 (there is only 1 maximum Task ID that is 3)
Any help much appreciated.
-
try this
SELECT userid,MAX(taskid) AS Maxtask
FROM tablename
GROUP BY userid
----------
Ashish -
DECLARE @t table (ID smallint, UserID smallint, TaskID smallint)insert into @t
select 100 , 1 , 1 union all
select 101 , 1 , 2 union all
select 102 , 1 , 3 union all
select 103 , 1 , 4 union all
select 104 , 1 , 5 union all
select 105 , 1 , 5 union all
select 106 , 1 , 6 union all
select 107 , 1 , 7 union all
select 108 , 1 , 7 union all
select 109 , 2 , 1 union all
select 110 , 2 , 1 union all
select 111 , 2 , 2 union all
select 111 , 2 , 2 union all
select 112 , 2 , 3 union all
select 113 , 2 , 4 union all
select 114 , 2 , 5 union all
select 115 , 2 , 6 union all
select 116 , 2 , 7 union all
select 117 , 2 , 8 union all
select 118 , 2 , 9 union all
select 119 , 2 , 12 union all
select 120 , 3 , 1 union all
select 121 , 3 , 2 union all
select 122 , 3 , 2 union all
select 123 , 4 , 1 union all
select 124 , 4 , 2 union all
select 125 , 4 , 3
select b.UserID, b.MaxTaskID
from
(select t.UserID, Max(TaskID) MaxTaskID
from @t t
group by UserID) b
inner join
(
select UserID, TaskID, count(TaskID) NumTasks
from @t
group by UserID, TaskID
having count(TaskID) = 1) a
on a.UserID = b.UserID
and a.TaskID = b.MaxTaskID
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
WITH CTE AS (
SELECT UserID, TaskID,
COUNT(*) OVER(PARTITION BY UserID,TaskID) AS cn,
ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY TaskID DESC) AS rn
FROM @t)
SELECT UserID,TaskID
FROM CTE
WHERE rn=1 AND cn=1;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
ashish.kuriyal (9/8/2010)
try thisSELECT userid,MAX(taskid) AS Maxtask
FROM tablename
GROUP BY userid
This is almost it, if only it was to give the User ID back where there is only 1 highest taskid.
Mike01 (9/8/2010)
DECLARE @t table (ID smallint, UserID smallint, TaskID smallint)insert into @t
select 100 , 1 , 1 union all
select 101 , 1 , 2 union all
select 102 , 1 , 3 union all
select 103 , 1 , 4 union all
select 104 , 1 , 5 union all
select 105 , 1 , 5 union all
select 106 , 1 , 6 union all
select 107 , 1 , 7 union all
select 108 , 1 , 7 union all
select 109 , 2 , 1 union all
select 110 , 2 , 1 union all
select 111 , 2 , 2 union all
select 111 , 2 , 2 union all
select 112 , 2 , 3 union all
select 113 , 2 , 4 union all
select 114 , 2 , 5 union all
select 115 , 2 , 6 union all
select 116 , 2 , 7 union all
select 117 , 2 , 8 union all
select 118 , 2 , 9 union all
select 119 , 2 , 12 union all
select 120 , 3 , 1 union all
select 121 , 3 , 2 union all
select 122 , 3 , 2 union all
select 123 , 4 , 1 union all
select 124 , 4 , 2 union all
select 125 , 4 , 3
select b.UserID, b.MaxTaskID
from
(select t.UserID, Max(TaskID) MaxTaskID
from @t t
group by UserID) b
inner join
(
select UserID, TaskID, count(TaskID) NumTasks
from @t
group by UserID, TaskID
having count(TaskID) = 1) a
on a.UserID = b.UserID
and a.TaskID = b.MaxTaskID
Hi Mike, the table I gave was an example - the one im going to run it against has almost 100k lines, the script would end up way too big im affraid.
Mark-101232 (9/8/2010)
WITH CTE AS (
SELECT UserID, TaskID,
COUNT(*) OVER(PARTITION BY UserID,TaskID) AS cn,
ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY TaskID DESC) AS rn
FROM @t)
SELECT UserID,TaskID
FROM CTE
WHERE rn=1 AND cn=1;
Hi Mark, thats excellent thanks, just what I was after!! Now off to find out how it works 🙂
-
Pete-L (9/8/2010)
Hi Mike, the table I gave was an example - the one im going to run it against has almost 100k lines, the script would end up way too big im affraid.Pete - the actual part you need is:
select b.UserID, b.MaxTaskIDfrom
(select t.UserID, Max(TaskID) MaxTaskID
from @t t
group by UserID) b
inner join
(
select UserID, TaskID, count(TaskID) NumTasks
from @t
group by UserID, TaskID
having count(TaskID) = 1) a
on a.UserID = b.UserID
and a.TaskID = b.MaxTaskID
All the rest was just what he had to go through to get your sample data into a table.
You would need to change the @t referenced above to your actual table.
You might also want to look at the first link in my signature for how to post data in such a way that you will get more people to look at it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply