Select Distinct record based on a field (I am using CTE)

• skb 44459

  SSCarpal Tunnel

  Points: 4768

  More actions

  June 14, 2013 at 12:32 pm

  #273980

  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