How to display Top records by user? 1

[egghi]

Hi,

I creatde a view, which contains three fields, Username, Number of Changes Made, FieldName. The table shows how many changes each user has made to each field. I would like to write a query which displays each user's top 3 fields (the 3 fields each user made most changes to).

I ran the query:

SET ROWCOUNT 10

SELECT UserName, [Number of Changes Made], FieldName
FRIN FRom v_Audit_FieldbyUser
ORDER BY [Number of Changes Made] DESC

SET ROWCOUNT 0

I only got 10 rows of data showing the top 10 fields changed by all users.

Thank you in advance!

 

[r937]

select UserName
     , [Number of Changes Made]
     , FieldName FRIN 
  from v_Audit_FieldbyUser as T
 where ( select count(*) 
           from v_Audit_FieldbyUser  
          where [Number of Changes Made] 
            > T.[Number of Changes Made] ) < 3

r937.com | rudy.ca

 

[egghi]

Dear r937,

Thank you so much for your help. I ran the query after coming back to work today, but I was still unable to get the correct result (only 3 rows of data were returned instead of showing the top 3 records by each user).

Please advise. Thank you in advance!

 

[r937]

well, i dunno, i've used that same sql on hundreds of queries, so unless i've made a typo...

could you please show a few rows of data from your table so i can build the same table over here and test it

r937.com | rudy.ca

 

[egghi]

Hi r937,

UserName FieldName NumberofChangesMade
Amy Instance 648
Amy Charges 515
Amy CoveredCharge 515
Amy Date of Service 513
Amy Charge Type 513
Amy Comment 1 510
Carole Notes 19
Celia Instance 36
Celia Notes 24
Celia CoveredCharge 21
Celia Charges 21
Celia Noncovered Charge20
Corrie Instance 284
Corrie CoveredCharge 207
Corrie Charges 207
Corrie Date of Service 178
Corrie Other Insurance 178

The result I would like to get:

UserName FieldName NumberofChangesMade

Amy Instance 648
Amy Charges 515
Amy CoveredCharge 515
Amy Date of Service 513
Amy Charge Type 513
Carole Notes 19
Celia Instance 36
Celia Notes 24
Celia CoveredCharge 21
Celia Charges 21
Corrie Instance 284
Corrie CoveredCharge 207
Corrie Charges 207
Corrie Date of Service 178
Corrie Other Insurance 178

 

[r937]

my apologies, yes, i did have an error in the query, it should be like this --

select UserName
     , NumberofChangesMade
     , FieldName FRIN 
  from egghi as T
 where ( select count(*) 
           from egghi  
          where UserName
              = T.UserName
            and NumberofChangesMade 
            > T.NumberofChangesMade ) < 3

the results are:

Amy     648   Instance
Amy     515   Charges
Amy     515   CoveredCharge
Carole   19   Notes
Celia    36   Instance
Celia    24   Notes
Celia    21   CoveredCharge
Celia    21   Charges
Corrie  284   Instance
Corrie  207   CoveredCharge
Corrie  207   Charges

r937.com | rudy.ca

 

[egghi]

Dear r937,

Thank you, thank you, and thank you! It worked out great!!

THANK YOU!!