SQL Help

[litton1]

Hi All,

I have a sub query. It returns duplicates on the contact number but I would just like the top contact number. How can I do this?

left join 
	
	(SELECT p.PartyID, sc1.SubCaseRef, pcn.ContactNumber
                        FROM SubCase sc1
                        inner join SubCaseItem sci1 ON sci1.SubCaseID = sc1.SubCaseID
                inner join Venue v1 ON sci1.VenueID = v1.VenueID
                left join Party p ON p.PartyID = v1.PartyID  
                left join PartyContactNumbers pcn ON pcn.PartyID = p.PartyID               
                where v1.VenueID = sci1.VenueID
               group by p.PartyID, sc1.SubCaseRef, pcn.ContactNumber
                ) 
                contactNumJoin ON a.RehabReference = contactNumJoin.SubCaseRef

Thanks,
L

Age is a consequence of experience

 

[imex]

Hi,

Try something like this:

with CTE as
(
    SELECT DISTINCT
        p.PartyID, 
        sc1.SubCaseRef, 
        pcn.ContactNumber,
        ROW_NUMBER() OVER(PARTITION BY sc1.SubCaseRef ORDER BY pcn.ContactNumber) as RowNum
    FROM SubCase sc1
    inner join SubCaseItem sci1 ON sci1.SubCaseID = sc1.SubCaseID
    inner join Venue v1 ON sci1.VenueID = v1.VenueID
    left join Party p ON p.PartyID = v1.PartyID  
    left join PartyContactNumbers pcn ON pcn.PartyID = p.PartyID               
    where v1.VenueID = sci1.VenueID
)

select --...
left join CTE 
    ON a.RehabReference = CTE.SubCaseRef and 
       CTE.RowNum = 1

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[litton1]

Thanks for your reply.

Ideally I wanted it in one statement.. I came up with. I left the join as it was and in the field select I used max … This removed the second result on the contact number.

select field1,,,, MAX(contactNumJoin.ContactNumber )as ConNumber
inner join ....

Thanks again.



Age is a consequence of experience