代码之家 › 专栏 › 技术社区 › RoverRoll

如何从SQL中的两行获取最大ID

sql-server sql

RoverRoll · 技术社区 · 7 年前

我用排名做了一个分区,这里是下面的查询

select rn=rank()over(
按patid排序按chargeid描述划分
)
,费用ID
,帕迪
,事务类型
,发布日期
来自[交易主报告]
其中patid=598
和服务日期='2017-05-17 00:00:00.0000000'
和cptcode='96365'
交易类型!='调整'
交易类型!='付款'
and transactionType='费用'
按ChargeID分组
,帕迪
,事务类型
,发布日期
输出:img src=“https://i.stack.imgur.com/bvsu5.jpg”alt=“table1”/>

逻辑为TransactionType='Charges'-TransactionType='Void Charges'
但是我需要保留最新的费用如果您看到上面的chargeid对于一行是不同的,我需要保留该chargeid作为我的输出。


下面是执行的另一个查询TransactionType!='作废费用'


select rn=rank()over(
按patid排序按chargeid描述划分
)
,费用ID
,帕迪
,事务类型
,发布日期
来自[交易主报告]
其中patid=598
和服务日期='2017-05-17 00:00:00.0000000'
和cptcode='96365'
交易类型!='调整'
交易类型!='付款'
交易类型!='无效费用'
and transactionType='费用'

输出:img src=“https://i.stack.imgur.com/4qqie.jpg”alt=“output2”/>

从输出中,我需要保持

输出:


逻辑为TransactionType='Charges'-TransactionType='Void Charges'
但是我需要保留最新的费用如果你看到上面的chargeid对于一行是不同的,我需要保留该chargeid作为我的输出。

下面是执行的另一个查询交易类型!='无效费用'

    SELECT RN = RANK() OVER (
        PARTITION BY PatID ORDER BY ChargeId DESC
        )
    ,chargeID
    ,patid
    ,transactiontype
    ,pOSTdATE
FROM [TransactionMasterReport]
WHERE patid = 598
    AND servicedate = '2017-05-17 00:00:00.0000000'
    AND cptcode = '96365'
    AND transactiontype != 'adjustments'
    AND transactiontype != 'payments'
    AND transactiontype != 'Voided Charges'
    AND transactiontype = 'Charges'


输出:


从输出我需要保持最大费用ID

2 回复 | 直到 7 年前

Shakeer Mirza 7 年前

试试这个

SELECT *
FROM (
    SELECT DENSE_RANK() OVER (
            PARTITION BY PatID ORDER BY ChargeId DESC
            ) [RN]
        ,chargeID
        ,patid
        ,transactiontype
        ,pOSTdATE
    FROM [TransactionMasterReport]
    WHERE patid = 598
        AND servicedate = '2017-05-17 00:00:00.0000000'
        AND cptcode = '96365'
        AND transactiontype != 'adjustments'
        AND transactiontype != 'payments'
        AND transactiontype = 'Charges'
    GROUP BY chargeID
        ,patid
        ,transactiontype
        ,pOSTdATE
    ) a
WHERE a.rn = 1

您可以相应地调整子查询中的Where条件

TheSQLGirl 7 年前

只需添加到WHERE子句中,即可选择rn=1的位置。

WITH cteRank AS
(select  RN = RANK() OVER(PARTITION BY PatID order by ChargeId desc), 

chargeID,patid,transactiontype,pOSTdATE from [TransactionMasterReport] where 

patid=598 and servicedate = '2017-05-17 00:00:00.0000000' and cptcode ='96365' 

AND transactiontype !='adjustments' AND transactiontype !='payments'  and 

transactiontype !='Voided Charges' AND  transactiontype = 'Charges')
SELECT * 
FROM cteRank
WHERE RN = 1