Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
105ben
Starting Member
16 Posts |
Posted - 2012-12-29 : 16:13:01
|
Hello,the following query will find the total number of occurrences of every person_id in the attendance table, and list the figure and each person_id. Then when the second select runs to return the maximum and the person_id that has that maximum, its returning the maximum figure ok but then the person_id is just the first one in the count_temp table, regardless of which one has the highest totalselect max(count), person_idfrom (select person_id, count(person_id) as countfrom attendance as agroup by a.person_id) as count_tempcan anyone see why? |
|
shenulal
Starting Member
11 Posts |
Posted - 2012-12-30 : 05:28:45
|
You cannot run the above query as it will have the aggregate function using along in the second select also. re-write like thisselect top 1 count,person_idfrom (select person_id, count(person_id) as countfrom #tmp as agroup by a.person_id) as count_temp order by count deschope this will solve your issue.. |
|
|
|
|
|