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
精彩评论