Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select Distinct record based on a field (I am using CTE) Expand / Collapse
Author
Message
Posted Friday, June 14, 2013 12:32 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, February 14, 2014 1:15 PM
Points: 86, Visits: 196
I would like to select unique records using childkey field. the resultset should not include any record with repeated childkey.

IF My Data looks like
ParentKey ChildKey
---------- ---------
PARENT1 CHILD1
PARENT1 CHILD2
PARENT2 CHILD1

Select Statement Result should be
ParentKey ChildKey
---------- ---------
PARENT1 CHILD1
PARENT1 CHILD2

HERE is the SP.
WITH KIDD (ParentKey, ChildKey)
AS
(
select KI_SLDrillDown.ParentKey, KI_SLDrillDown.ChildKey
from KI_SLDrillDown
where KI_SLDrillDown.ChildKey = ('O_123456' )
and KI_SLDrillDown.ParentKey is NULL
union ALL
select KI_SLDrillDown.ParentKey, KI_SLDrillDown.ChildKey from KI_SLDrillDown
inner join KIDD as d
on KI_SLDrillDown.ParentKey = d.ChildKey
)
SELECT ParentKey, ChildKey
Post #1463731
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse