CODEWITHSUNDEEP
sqlsql-servert-sql
Chantelle Brink
Chantelle Brink

Reputation: 93

Highest Record for a set user

Hope someone can help. I have been trying a few queries but I do not seem to be getting the desired result.

I need to identify the highest ‘’claimed’’ users within my table without discarding the columns from the final report.

The user can have more than one record in the table, however the data will be completely different as only the user will match.

The below query only provides me the count per user without giving me the details. 

  SELECT User, count (*) total_record
  FROM mytable
  GROUP BY User
  ORDER BY count(*) desc

Table: 

 mytable
 Column 1 = User Column 2 = Ref Number Column 3 = Date

The first column will be the unique identifier, however the data in the other columns will differ, therefore it needs to descend the highest claimed user with all the relevant rows to the user to the least claimed user.

User|Ref Num|Date   
1|a|20150317
1|b|20150317
2|c|20150317
3|d|20150317
4|e|20150317
1|f|20150317
4|e|20150317

The below data is how the values should be returned. 

User|Ref Num|Date|Count 
1|a|20150317|3
1|b|20150317|3
1|f|20150317|3
2|c|20150317|1
3|d|20150317|1
4|e|20150317|2
4|e|20150317|2

Hope it makes sense.

Thank you

Upvotes: 0

Views: 66

Answers (5)

legohead
legohead

Reputation: 540

As you're using MSSQL you can use the OVER() clause like so: 

SELECT  [user], mt.ref_num, mt.[date], COUNT(mt.[user]) OVER(PARTITION BY mt.[user])
FROM    myTable mt

More about the OVER clause can be found here: https://msdn.microsoft.com/en-us/library/ms189461.aspx

As per your comment you can use the wildcard * like so:

SELECT  mt.*, COUNT(mt.[user]) OVER(PARTITION BY mt.[user])
FROM    myTable mt

This would get you every column as well as the result of the count.

Upvotes: 1

jpw
jpw

Reputation: 44891

You could use an outer apply if your version of SQL Server supports it:

SELECT [User], [Ref Num], Date, total_record
FROM mytable M
OUTER APPLY (
  SELECT count(*) total_record 
  FROM mytable 
  WHERE [user] = M.[user] 
  GROUP BY [user]
) oa
ORDER BY total_record desc, [user]

Note that user is a reserved keyword in MSSQL and you need to enclose it in either brackets [user] or double-quotes "user".

This would produce an output like:

user    Ref Num Date    total_record
1       a   2015-03-17  3
1       b   2015-03-17  3
1       f   2015-03-17  3
4       e   2015-03-17  2
4       e   2015-03-17  2
2       c   2015-03-17  1
3       d   2015-03-17  1

Note that the answers using the count(*) OVER (partition by [user]) construct are more efficient though.

Upvotes: 1

Hugo Yates
Hugo Yates

Reputation: 2111

This is the old way of doing it. Where possible you should use OVER but as other people have answered with that I thought I'd throw this one into the mix.

SELECT
     T.[User]
    ,T.[Ref Num]
    ,T.[Date]
    ,(SELECT count(*) from [myTable] T2 where T2.[User] = T.[USER]) as [Count]
FROM [mytable] T
ORDER BY [Count] DESC

Upvotes: 0

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17157

Most simple way would be to use window fuction.

SELECT table.*, COUNT(*) OVER (PARTITION BY user)
FROM nameoftable table -- this is an alias
ORDER BY user, ref_num

This also seem to fit your need.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270071

If you want to order by the number of record for each user, then use window functions instead of aggregation:

SELECT t.*
FROM (SELECT t., count(*) OVER (partition by user) as cnt
      FROM mytable t
     ) t
ORDER BY cnt DESC, user;

Note that I added user to the order by so users with the same count will appear together in the list.

Upvotes: 1

Related Questions