Trying to get max(count)

Transact-SQL
1

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

2

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
3

Sorry. I should have mentioned that I have multipe siteNames

4

You can use the row_number function.

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;
5

This worked perfectly. Thanks man.