如何按字段值的出现次数对记录集进行排序并按其排序 - How to order recordset by number of occurrences of a field value and sort by it

How to order recordset by number of occurrences of a field value and sort by it

本文关键字：排序记录字段何按 | 更新日期: 2023-09-27

我正在运行一个查询 select * from transactions给了我记录集

ppno ---------name---------------amnt
1309 ---------Rajasekar----------12000
1210 ---------Mani     ----------21000
1309 ---------Sank     ----------90012
1100 ---------still    ----------12232
1309 ---------Jack     ----------23344

我想按同一ppno的出现次数对结果集进行分组，并根据出现次数对其进行排序。例如，我想要这样的东西。

ppno ---------name---------------amnt
1309 ---------Rajasekar----------12000
1309 ---------Sank     ----------90012
1309 ---------Jack     ----------23344
1210 ---------Mani     ----------21000    
1100 ---------still    ----------12232

select t.*
from transactions t
order by (
    select count(1) from transactions t2 where t2.ppno = t.ppno
) desc, t.ppno desc

SELECT t.ppno, t.name, t.amnt
    FROM (SELECT ppno, COUNT(*) AS ppnoCount
              FROM transactions
              GROUP BY ppno) c
        INNER JOIN transactions t
            ON c.ppno = t.ppno
    ORDER BY c.ppnoCount DESC, t.ppno