SQL Group By Max of Group By Count

Question

这是我的初始计数查询和结果集。我只是不知道如何得到最大查询工作。

查询

SELECT QM_CUST_CODE, QM_XX_REGION, COUNT(QM_QUOTE_NUM) as Quotes
FROM QM (NOLOCK)
WHERE QM_CUST_CODE in ('H-PAT001', 'H-ABI002')
GROUP BY QM_CUST_CODE, QM_XX_REGION

结果

QM_CUST_CODE    QM_XX_REGION    Quotes
1  H-ABI002     0           1
2  H-PAT001     0           3
3  H-ABI002     1           397
4  H-PAT001     1           1509
5  H-PAT001     2           1

——这个问题我如何只得到第3行和第4行返回，因为这些是按地区的客户代码，具有最大数量的报价。

Answer 1

WITH recordsList
AS
(
    SELECT  QM_CUST_CODE, QM_XX_REGION, COUNT(QM_QUOTE_NUM) as Quotes,
            DENSE_RANK() OVER  (PARTITION BY QM_CUST_CODE 
                                ORDER BY COUNT(QM_QUOTE_NUM) DESC) rn
    FROM    QM
    WHERE   QM_CUST_CODE in ('H-PAT001', 'H-ABI002')
    GROUP   BY QM_CUST_CODE, QM_XX_REGION
)
SELECT  QM_CUST_CODE, QM_XX_REGION, Quotes
FROM    recordsList
WHERE   rn = 1

Answer 2

如果你只想得到两行有最高的引号计数，然后试试这个:

Select Top 2 * From
(
SELECT QM_CUST_CODE, QM_XX_REGION, COUNT(QM_QUOTE_NUM) as Quotes
FROM QM
WHERE QM_CUST_CODE in ('H-PAT001', 'H-ABI002')
GROUP BY QM_CUST_CODE, QM_XX_REGION
)tbl
Order By  Quotes Desc