The below query is returning rows with duplicated "arrestno"s because there are multiple comments for each record. I need to modify it to return only the row for each arrestno with the most recent comment entered. This comment field is h.comment (near the end of the select list), and the date field is h.commentdatetime (not currently used in the query).
I know this involves partitioning the data and choosing the first row number based on this date field. But there are so many columns and joins involved in this query, I can't get the syntax down. Your help is greatly appreciated.
SELECT b.namelast + ', ' + b.namefirst + ' ' + b.namemiddle as 'NAME', b.namelast,b.namemiddle,b.namefirst, a.reportnumber as arrestno,a.courtcasenumber as courtcase,b.dateofbirth as dob,b.race,b.sex,b.ssn,isnull(c.streetnumber,'') + ' ' + isnull(c.street,'') + ' ' + isnull(c.city,'') + ' ' + isnull(c.state,'') + ' ' + isnull(c.zipcode,'') as 'Address', C.street,c.city,c.state,c.zipcode,c.pgeo,c.lawgeo1 as zone,c.lawgeo2 as distgrid,a.reportdatetime as reportdate, d.chargeleveldescription,d.chargedegreedescription,d.chargecounts as counts,d.charge as fsn,d.chargearrestoffensecodevalue as fciccodev,a.othernumber,a.IncidentOccurFromDateTime, e.officername,e.officercallno,e.officerorgunit,f.i_gangass,f.i_gangmem,f.i_regsxpr,f.i_consxof,f.i_violent,f.i_regcrim,f.i_prisrel,h.Comment,left(j.probablecause,300) as probcause FROM table1 a INNER JOIN table2 b on a.uniquekey = b.WarrantUniqueFKey INNER JOIN table3 c on b.AddressUniqueFKey = c.uniquekey INNER JOIN table4 d on a.uniquekey = d.WarrantUniqueFKey LEFT JOIN table5 e on a.uniquekey = e.WarrantUniqueFKey INNER JOIN table6 f on b.mnino = f.MNINO LEFT JOIN table7 h on a.uniquekey = h.WarrantUniqueFKey INNER JOIN table8e j on a.uniquekey = j.WarrantUniqueFKey INNER JOIN dbo.Split(@year, ',') as I ON a.reportnumber like I.Data + '%' WHERE b.IsWarrantPrimaryPerson = '1' AND a.ReportStatusUniqueFKey = 'gesRx2bTV0AtsL24bk0ByA' AND (a.ReportTypeUniqueFKey = '0Pg7HcBQd06XqPaX0HYhVQ' or a.ReportTypeUniqueFKey = 'O0bQw17dXUyiFTSuSN6AcA') ORDER BY b.namelast + ', ' + b.namefirst + ' ' + b.namemiddle