SQLTeam.com | Weblogs | Forums

Distinct not working for me


#1

Sorry, I don't know how to put my code on here properly.
The below code works. I want to modify it to order by ADDED_DTE from WO_HDR. I changed the join line to:

join (select distinct ACCTNO, ADDED_DTE from WO_HDR where ADDED_DTE > '2014-01-01') as t1 on (t1.ACCTNO = cv.ACCTNO)

and then I changed the order by line to:

order by t1.added_dte desc

but I received a lot of repeat lines due to different added_dte per acctno. Help

` select cv.ACCTNO
, cv.NAME
, cs.search1 as [Type]

from CUSTVEND cv
join (select distinct ACCTNO from WO_HDR where ADDED_DTE > '2014-01-01') as t1 on (t1.ACCTNO = cv.ACCTNO)
left outer join CUSTSEARCH cs on (cv.ACCTNO = cs.ACCTNO)

where (cs.SEARCH1 is null or cs.SEARCH1 = '')
order by ACCTNO desc `


#2

when you get repeated lines from a join, it is often because you are not joining on enough columns. Check that. As a last resort, put DISTINCT on the outer query.

I try to avoid DISTINCT in production queries because it often results in a sort operation.


#3

Yeah, using DISTINCT if you are getting more rows than you expect to fix the problem is one of those code smells to be avoided. It's always better to find the cause of the extra rows than to band-aid the issue with DISTINCT. I see this a lot and it is always troublesome.


#4

I battle this all the time due to a poorly designed DB. I have a patient visit the hospital and they have one visit ID. However, they may be billed multiple times and the invoice numbers are in a table where they are the primary key. The visit ID is there as well, but trying to find invoices by the visit ID results in multiple returns often separated from the others by a different field each time. I end up solving the problem with either MAX() or TOP 1 in the select.


#5

It looks like you need to decide which row from WO_HDR to show.
Something like the following will show the most recent row.

SELECT *
FROM CUSTVEND V
    LEFT JOIN CUSTSEARCH S ON V.ACCTNO = S.ACCTNO
    CROSS APPLY
    (
        SELECT TOP (1) W.ACCTNO, W.ADDED_DTE
        FROM WO_HDR W
        WHERE W.ACCTNO = V.ACCTNO
            AND ADDED_DTE > '2014-01-01'
        ORDER BY W.ADDED_DTE DESC
    ) A
ORDER BY V.ACCTNO DESC;