TSQL——选择计数较高的行，当计数相同时，选择id值较高的行

HELP!!! I'm stumped and have tried several options to no avail... I need to return one row for each Pub_id, and the row that is returned should be the one with the higher Count and when there is more than one row with the highest count, I need the one with the higher price_id.

帮助! ! !我被难住了，尝试了好几种选择，但都无济于事。我需要为每个Pub_id返回一行，返回的行应该是计数较高的行，当计数最高的行多于一行时，我需要具有较高price_id的行。

I have populated a table with this data...

我用这些数据填充了一个表……

pub_id, price_id,   count
7,  59431,            5
22, 39964,            4
39, 112831,           3
39, 120715,           2
47, 95359,            2
74, 142825,           5
74, 106688,           5
74, 37514,            1

---

and This is what I need to return...

这就是我需要返回的。

pub_id, price_id,   count
7,  59431,            5
22, 39964,            4
39, 112831,           3
47, 95359,            2
74, 142825,           5

2 个解决方案

#1

;WITH T
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY pub_id 
                               ORDER BY [count] DESC, price_id DESC) AS rn
         FROM   your_table)
SELECT pub_id,
       [count],
       price_id
FROM   T  
WHERE rn=1

#2

Do you want something like this

你想要这样的东西吗

select pub_id,
       Count,
       Price_Id
from   (select Pub_id,
               max(count),
               Price_Id
        from   table_name
        group  by Pub_id) der_tab
group  by Pub_id,
          Count
having Price_id = max(price_Id)