多列的Distinct和order by

给,你可以用 Row_number 函数为结果集分配排名或Row_顺序:

SELECT *
FROM
(
    SELECT ID,
           [Sid],
           [Point],
           [priority1],
           JoinDate,
           Title,
           Content,
           ROW_NUMBER() OVER(PARTITION BY [Sid] ORDER BY [priority1] desc, [Point] desc) AS RN
    FROM <your_resultset>
) a where a.RN = 1;

结果:

您可以使用cte和 row_number 以获得以下所需结果。

 WITH cte as
  (SELECT t1.*, ROW_NUMBER() over(PARTITION BY sid
                                  ORDER BY priority DESC, point DESC) AS rn
   FROM table1 t1)
SELECT id,
       sid,
       point,
       priority --- and other columns
FROM cte
WHERE rn = 1

id  sid     point   priority
---------------------------
7   1509    10      3
9   1510    45      2

你可以查看演示 here

SELECT Sid,
       MAX(Point) KEEP (DENSE_RANK FIRST ORDER BY (priority1, Point)) as top_point,
       MAX(priority1) KEEP (DENSE_RANK FIRST ORDER BY (priority1, Point)) as top_priority,
       ....
FROM (The query you have)
GROUP By Sid

在此处阅读更多信息 https://docs.oracle.com/cd/B19306_01/server.102/b14223/analysis.htm#DWHSG0205

我想你说的distinct是指一个值,所以它是分组的基础。

更新

https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql

SELECT
  Sid, priority1, Point,
  row_number() over(partition by Sid order by priority1, Point) as roworder
FROM (the query) 
HAVING roworder=1 ;