March 28, 2018 at 6:32 pm
I am trying to pull several fields and get a total count which I was able to do. However, what I really need is only to pull the record with the MAX count. The query is below. How do I modify to ONLY get the row of data with the MAX PARCEL_COUNT. How do I do a Max and a Count on the same field?
select COUNT(distinct P.PARID) as PARCEL_COUNT, A.AUTH,A.USER1, A.NAME, D.DISTCODE, D.TOTALRATE, P.JUR
from RTAUTH A, RTDIST D, PARDIST P
where A.TAXYR = '&TAXYR' and A.AUTHTYPE = 'MUN'
and A.JUR = D.JUR
and A.TAXYR = D.TAXYR
and D.JUR = P.JUR
and D.TAXYR = p.taxyr
and D.DISTCODE = P.DISTCODE
and P.CUR = 'Y'
and A.USER1 = 'MUN_ANTIO'
group by A.AUTH, A.USER1, A.NAME, D.DISTCODE, D.TOTALRATE,P.JUR
order by parcel_count desc
March 28, 2018 at 7:19 pm
One way of doing this is to wrap your query in a TOP predicate --- I'm using AdventureWorks2014 (because being able to test my answers hopefully keeps me from making an idiot of myself)
I'm ordering by COUNT(DISTINCT(ProductID)) and then I'm taking the first value, and since I'm sorting by that expression, the highest value is the first value, so I can just use TOP 1.
SELECT TOP 1
h.CustomerID
, COUNT(DISTINCT(ProductID)) AS DistinctProducts
FROM Sales.SalesOrderDetail d INNER JOIN
Sales.SalesOrderHeader h ON d.SalesOrderID = h.SalesOrderID
GROUP BY h.CustomerID
ORDER BY COUNT(DISTINCT(ProductID)) DESC;
March 29, 2018 at 7:16 am
pietlinden - Wednesday, March 28, 2018 7:19 PMOne way of doing this is to wrap your query in a TOP predicate --- I'm using AdventureWorks2014 (because being able to test my answers hopefully keeps me from making an idiot of myself)
I'm ordering by COUNT(DISTINCT(ProductID)) and then I'm taking the first value, and since I'm sorting by that expression, the highest value is the first value, so I can just use TOP 1.
SELECT TOP 1
h.CustomerID
, COUNT(DISTINCT(ProductID)) AS DistinctProducts
FROM Sales.SalesOrderDetail d INNER JOIN
Sales.SalesOrderHeader h ON d.SalesOrderID = h.SalesOrderID
GROUP BY h.CustomerID
ORDER BY COUNT(DISTINCT(ProductID)) DESC;
You can ORDER BY the alias of the count, DistinctProducts, rather than putting in the count again.ORDER BY is processed after just about everything else in a query, so the alias will work there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 29, 2018 at 7:47 am
This is not working. It is telling me that From keyword not where expected.
select top 1
A.AUTH, COUNT(distinct P.PARID) as PARCEL_COUNT,A.USER1, A.NAME, D.DISTCODE, D.TOTALRATE, P.JUR
from RTAUTH A, RTDIST D, PARDIST P
where A.TAXYR = '&TAXYR' and A.AUTHTYPE = 'MUN'
and A.JUR = D.JUR
and A.TAXYR = D.TAXYR
and D.JUR = P.JUR
and D.TAXYR = p.taxyr
and D.DISTCODE = P.DISTCODE
and P.CUR = 'Y'
and A.USER1 = 'MUN_ANTIO'
group by A.AUTH, A.USER1, A.NAME, D.DISTCODE, D.TOTALRATE,P.JUR
order by Parcel_COUNT desc
March 29, 2018 at 8:17 am
Please post the exact error message you are getting,
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 29, 2018 at 9:12 am
summerfancher - Thursday, March 29, 2018 7:47 AMThis is not working. It is telling me that From keyword not where expected.
select top 1
A.AUTH, COUNT(distinct P.PARID) as PARCEL_COUNT,A.USER1, A.NAME, D.DISTCODE, D.TOTALRATE, P.JUR
from RTAUTH A, RTDIST D, PARDIST P
where A.TAXYR = '&TAXYR' and A.AUTHTYPE = 'MUN'
and A.JUR = D.JUR
and A.TAXYR = D.TAXYR
and D.JUR = P.JUR
and D.TAXYR = p.taxyr
and D.DISTCODE = P.DISTCODE
and P.CUR = 'Y'
and A.USER1 = 'MUN_ANTIO'
group by A.AUTH, A.USER1, A.NAME, D.DISTCODE, D.TOTALRATE,P.JUR
order by Parcel_COUNT desc
I suggest that you start learning to use the new JOIN syntaxSELECT TOP(1)
A.AUTH
, PARCEL_COUNT = COUNT( DISTINCT P.PARID )
, A.USER1
, A.NAME
, D.DISTCODE
, D.TOTALRATE
, P.JUR
FROM RTAUTH AS A
INNER JOIN RTDIST AS D
ON A.JUR = D.JUR
AND A.TAXYR = D.TAXYR
INNER JOIN PARDIST AS P
ON D.JUR = P.JUR
AND D.TAXYR = P.taxyr
AND D.DISTCODE = P.DISTCODE
WHERE A.TAXYR = '&TAXYR'
AND A.AUTHTYPE = 'MUN'
AND A.USER1 = 'MUN_ANTIO'
AND P.CUR = 'Y'
GROUP BY A.AUTH, A.USER1, A.NAME, D.DISTCODE, D.TOTALRATE, P.JUR
ORDER BY PARCEL_COUNT DESC;
March 29, 2018 at 9:17 am
summerfancher - Thursday, March 29, 2018 7:47 AMThis is not working. It is telling me that From keyword not where expected.
select top 1
A.AUTH, COUNT(distinct P.PARID) as PARCEL_COUNT,A.USER1, A.NAME, D.DISTCODE, D.TOTALRATE, P.JUR
from RTAUTH A, RTDIST D, PARDIST P
where A.TAXYR = '&TAXYR' and A.AUTHTYPE = 'MUN'
and A.JUR = D.JUR
and A.TAXYR = D.TAXYR
and D.JUR = P.JUR
and D.TAXYR = p.taxyr
and D.DISTCODE = P.DISTCODE
and P.CUR = 'Y'
and A.USER1 = 'MUN_ANTIO'
group by A.AUTH, A.USER1, A.NAME, D.DISTCODE, D.TOTALRATE,P.JUR
order by Parcel_COUNT desc
where A.TAXYR = '&TAXYR' --Are you sure about this part. I think it might be wrong
Saravanan
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply