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-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:52 PM
Points: 101, Visits: 236
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