I realize it can't be done. In the code below. I neeed to bring only this record back because it has the highest count for that SiteName "Avn-A"
678 Avn-A GA <<<<<<<---------------------------------
10 Avn-A HD
2 Avn-A JH
1 Avn-A KN
Here is my sql
SELECT
COUNT(dbo.tbl_LUEmployingActivities.Id) AS Expr1,
dbo.tblSite.SiteName,
dbo.tbl_LUEmployingActivities.EmployingActivity
FROM
dbo.tblEmployee
INNER JOIN
dbo.tblOrganizations
ON dbo.tblEmployee.OrganizationId = dbo.tblOrganizations.Id
INNER JOIN
dbo.tblSite
ON dbo.tblOrganizations.SiteID = dbo.tblSite.Id
INNER JOIN
dbo.tblEmployeeDoDCiv
ON dbo.tblEmployee.Id = dbo.tblEmployeeDoDCiv.Id
INNER JOIN
dbo.tbl_LUEmployingActivities
ON dbo.tblEmployeeDoDCiv.EmployingActivityId = dbo.tbl_LUEmployingActivities.Id
GROUP BY
dbo.tbl_LUEmployingActivities.EmployingActivity,
dbo.tblSite.SiteName
having dbo.tbl_LUEmployingActivities.EmployingActivity <> 'H9'
ORDER BY
--COUNT(dbo.tbl_LUEmployingActivities.Id) DESC
dbo.tblSite.SiteName,dbo.tbl_LUEmployingActivities.EmployingActivity
Not clear to me why you assert that it can't be done. Regardless, based on what you have posted, if you want to get just the first record, add a TOP (1) to your query like this. Be sure to have the correct ORDER BY clause that would guarantee that record to be at the top (i.e., uncomment the second to last line you have commented out in your query)
SELECT TOP (1)
COUNT(dbo.tbl_LUEmployingActivities.Id) AS Expr1,
dbo.tblSite.SiteName,
-- etc. rest of your query
SELECT * FROM
(
SELECT COUNT(dbo.tbl_LUEmployingActivities.Id) AS Expr1 ,
dbo.tblSite.SiteName ,
dbo.tbl_LUEmployingActivities.EmployingActivity,
ROW_NUMBER() OVER
(
PARTITION BY dbo.tblSite.SiteName
ORDER BY COUNT(dbo.tbl_LUEmployingActivities.Id) DESC
) AS N
FROM dbo.tblEmployee
INNER JOIN dbo.tblOrganizations ON dbo.tblEmployee.OrganizationId = dbo.tblOrganizations.Id
INNER JOIN dbo.tblSite ON dbo.tblOrganizations.SiteID = dbo.tblSite.Id
INNER JOIN dbo.tblEmployeeDoDCiv ON dbo.tblEmployee.Id = dbo.tblEmployeeDoDCiv.Id
INNER JOIN dbo.tbl_LUEmployingActivities ON dbo.tblEmployeeDoDCiv.EmployingActivityId = dbo.tbl_LUEmployingActivities.Id
GROUP BY dbo.tbl_LUEmployingActivities.EmployingActivity ,
dbo.tblSite.SiteName
HAVING dbo.tbl_LUEmployingActivities.EmployingActivity <> 'H9'
) AS s
WHERE N = 1
ORDER BY
SiteName ,
EmployingActivity;