sql server 2012 -获取列的最大值，列的最小值对应的排名在ms sql一次

Question

我有这个查询

WITH summary AS 
(
   SELECT  Msisdn, DateRegistered , 
       RANK() OVER  (ORDER BY DateRegistered ASC) AS dRank 
   FROM dbo.SubscriptionsArchive 
   WHERE MSISDN='123456'
)
SELECT s.msisdn, s.DateRegistered AS firstReg 
FROM summary s 
WHERE dRank =(SELECT max(dRank) FROM summary )

这显示了与最小Rank对应的第一个streg，我想同时获得与maxRank对应的最后一个reg。我该如何做到这一点?

Answer 1

使用TOP 1语法交叉连接CTE的第一行和最后一行的解决方案:

WITH summary AS 
(
   SELECT  Msisdn, DateRegistered , 
       RANK() OVER  (ORDER BY DateRegistered ASC) AS dRank 
   FROM dbo.SubscriptionsArchive 
   WHERE MSISDN='123456'
)
SELECT minrow.*, maxrow.*
FROM 
   (select TOP 1 * from summary order by dRank desc) minrow
   CROSS JOIN
   (select TOP 1 * from summary order by dRank asc) maxrow ;

Answer 2

WITH summary AS 
(
   SELECT  Msisdn, DateRegistered , 
       RANK() OVER  (ORDER BY DateRegistered ASC) AS ASCRank, 
       RANK() OVER  (ORDER BY DateRegistered DESC) AS DESCRank 
   FROM dbo.SubscriptionsArchive 
   WHERE MSISDN='123456'
)
SELECT s.msisdn,
(CASE WHEN ASCRAnk=1 THEN s.DateRegistered END)  AS firstReg,
(CASE WHEN DESCRAnk=1 THEN s.DateRegistered END)  AS LASTReg
FROM summary s