开发者

select statement that selects distinct based on rownumber

I have a very simple query, but some booking columns repeat and if they do i do not want to show them, however the entire row is not the same how can i ma开发者_Go百科ke it so if the row is a duplicate show the one with the max total.

SELECT     PREMIUMYEAR, 
           LASTNAME, 
           FIRSTNAME, 
           BOOKING, 
           CLAIMTYPE, 
           DEPARTUREDATE, 
           [PLAN], 
           INCIDENT, 
           RESERVEAMOUNT, 
           FINALSTATUS, 
           AGE, 
           TOTAL
FROM       dbo.[table]


SELECT PREMIUMYEAR, LASTNAME, FIRSTNAME, BOOKING, CLAIMTYPE, DEPARTUREDATE, [PLAN], INCIDENT, RESERVEAMOUNT, FINALSTATUS, AGE, TOTAL
    FROM (SELECT PREMIUMYEAR, LASTNAME, FIRSTNAME, BOOKING, CLAIMTYPE, DEPARTUREDATE, [PLAN], INCIDENT, RESERVEAMOUNT, FINALSTATUS, AGE, TOTAL,
                 ROW_NUMBER() OVER(PARTITION BY BOOKING ORDER BY TOTAL DESC) AS RowNum
              FROM dbo.[table]) t
    WHERE t.RowNum = 1
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜