Selecting only one row from group

Transact-SQL
1

Hi all,

From my query a result set like this is returned.

INCIDENT NO. OFFENSE NO. COMPLAINT DISP3 DATE ADDRESS
10 20 ... .... .... ...
10 25 ..... .... .... .....
10 30 .... ..... .... ....
20 50 .... ..... .... ....

I do not want to show any duplicates where all the fields are the same, nor do I want to show rows where they're the same except for the one field OFFENSE NO. I believe this is accomplished with row_number function along with partition by, but I'm not sure how to implement it.

SELECT a.incidentno as 'Incident No.', c.offenseno as 'Offense No.', a.Complaint, a.code3 as 'Disp3',
a.dtrecv as 'Date', a.caddress1 + ' APT/LOT#' + a.captlot as Address
FROM CALLHIST a LEFT OUTER JOIN CADCALL_UNIT b ON a.INCIDENTNO = b.INCIDENTNO
LEFT OUTER JOIN [###].dbo.[###] c ON a.INCIDENTNO = c.CAD_INCID LEFT OUTER JOIN [###].dbo.[###] d ON b.perno = d.perno
WHERE a.dtrecv >= '1/1/2002' and a.dtrecv <= '7/23/2015' order by a.dtrecv

Thanks.

2

Sorry, my field values in the table didn't indent and line up with the header.

3 
SELECT incidentno, [Offense No.], Complaint, Disp3, Date, Address
FROM (
    SELECT a.incidentno as 'Incident No.', c.offenseno as 'Offense No.', a.Complaint, a.code3 as 'Disp3', 
    a.dtrecv as 'Date', a.caddress1 + ' APT/LOT#' + a.captlot as Address,
    ROW_NUMBER() OVER(PARTITION BY a.incidentno, a.Complaint, a.code3, a.dtrecv, a.caddress1, a.captlot ORDER BY a.dtrecv) AS row_num
    FROM CALLHIST a 
    LEFT OUTER JOIN CADCALL_UNIT b ON a.INCIDENTNO = b.INCIDENTNO 
    LEFT OUTER JOIN [###].dbo.[###] c ON a.INCIDENTNO = c.CAD_INCID 
    LEFT OUTER JOIN [###].dbo.[###] d ON b.perno = d.perno 
    WHERE a.dtrecv >= '20020101' and a.dtrecv <= '20150723' 
) AS derived
ORDER BY Date
4

Thanks Scott. but I still receive duplicate rows of the same 'incidentno' but differnent 'offense no'. I only want to see one.

5

Hi,

If you wish to remove duplicates where all columns match apart from 'offense no', which record would you wish to return, e.g.
incident no | offence no
1 2
1 3
Which record would remain once duplicate is removed, 1 | 2 or 1 | 3 ?

6

Add this to Scott's code:

) AS derived
WHERE row_num = 1    -- <<<< ADD THIS LINE
ORDER BY Date

Change the ORDER BY within the OVER(xxx) statement if you need to change WHICH value for c.offenseno value is displayed when there are duplicates

7

DOH, quite right, I forgot the:
WHERE row_num = 1
in the outer query.

1 Like