I have a SQL that I am trying to resolve in one SQL. The situation is that I want to get a standing group by one field. However, I want to add a second field to it. That second field should affect the grouping as I only one one occurence of that second field to be calculcated.

We have a MessLog table which may have data like this:

NoThread2 AddUser OtherField
----------------------------
   000001  000001          1
   000001  000002          2
   000002  000001          1
   000003  000001          1
   000003  000002          2
   000004  000001          1
   000004  000001          2
   000004  000001          3
   000004  000002          1
   000004  000003          1

The standing I'm looking for is:

Thread Count
------------
000004     3
000001     2
000003     2
000002     1

The situation is that I want to get unique combination of NoThread2 and AddUser. As you can see with the NoThread2=000004, AddUser=1 is having 3 records for that one. So, that should count as only one as it's the same individual.

So far, I've been thinking of the following:

SELECT NoThread2,AddUser,COUNT(*) AS Compte FROM MessLog;
 GROUP BY NoThread2,AddUser INTO CURSOR Temp
SELECT TOP 50 NoThread2,COUNT(AddUser) AS Compte FROM Temp;
 GROUP BY 1 ORDER BY 2 DESC INTO CURSOR Temp

There must be something I'm missing here. I can't remember how to achieve that in one SQL.