Help with "row_number over partition"

1

Hi,

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
2

You could use either (1) OUTER APPLY, which is more efficient or (2) PARTITION with ROW_NUMBER().

--LEFT JOIN table7 h on a.uniquekey = h.WarrantUniqueFKey 
--replace this with either (1) or (2)

    OUTER APPLY (
        SELECT TOP (1) h.*
        FROM table7 h
        WHERE a.uniquekey = h.WarrantUniqueFKey
        ORDER BY h.commentdatetime DESC
    ) AS h

Or:

    LEFT JOIN (
        SELECT h.WarrantUniqueFKey, h.Comment, ROW_NUMBER() OVER(PARTITION BY h.WarrantUniqueFKey ORDER BY h.commentdatetime DESC) AS row_num
        FROM table7 h 
    ) AS h on a.uniquekey = h.WarrantUniqueFKey AND h.row_num = 1
1 Like
3

Thank Scott. You're always a tremendous help.