通过查询获取组中每个记录的最大值

I have a query in SQL looks like that:

我的SQL查询是这样的:

 select fldCustomer, fldTerminal, COUNT(fldbill)
 from tblDataBills
 group by fldCustomer, fldTerminal
 order by fldCustomer

results looks like:

结果看起来像:

 fldCustomer    fldTerminal (number of bills)
 0  1   19086
 0  2   10
 0  5   236
 1  1   472
 1  5   3
 1  500 19
 2  1   292
 2  500 22

how can i get the MAX count of each customer so i get results like

如何才能得到每个客户的最大计数，从而得到像这样的结果

 0 1 19086
 1 1 472
 2 1 292

Thanks in advance!

提前谢谢!

2 个解决方案

#1

Use a subquery with row_number():

使用带有row_number()的子查询:

select fldCustomer, fldTerminal, cnt
from (select fldCustomer, fldTerminal, COUNT(*) as cnt,
             row_number() over (partition by fldCustomer order by count(*) desc) as seqnum
      from tblDataBills
      group by fldCustomer, fldTerminal
     ) db
where seqnum = 1
order by fldCustomer ;

Note that in the event of ties, this will arbitrarily return one of the rows. If you want all of them, then use rank() or dense_rank().

注意，在连接事件中，这将会任意地返回其中的一行。如果您想要全部，那么使用rank()或dense_rank()。

#2

This might require a little trickery with the RANK() function

这可能需要使用RANK()函数进行一些技巧


SELECT fldCustomer, fldTerminal, [(number of bills)]
FROM (
    SELECT fldCustomer, fldTerminal, COUNT(fldbill) [(number of bills)],
        RANK() OVER (PARTITION BY fldCustomer ORDER BY COUNT(fldbill) DESC) Ranking
    FROM tblDataBills
    GROUP BY fldCustomer, fldTerminal
) a
WHERE Ranking = 1

#1