Forum Discussion
How to Display 1 data from Duplicate Data using List ?
Hy Friends,
I have problem with my Query
I want to display only 1 when it show duplicate
Here's my code:
SELECT OBS_ParRef,
OBSAT_ParRef,
OBS_ParRef->MRADM_ADM_DR->PAADM_RowID,
OBS_ParRef->MRADM_ADM_DR->PAADM_PAPMI_DR->PAPMI_RowId,
List(a) as No_Urut,
List(b) as No_KK,
List(c) as Hub_RT,
List(d) as Tempat_Mati
FROM
(SELECT OBS_ParRef,
OBSAT_ParRef,
OBS_ParRef->MRADM_ADM_DR->PAADM_RowID,
OBS_ParRef->MRADM_ADM_DR->PAADM_PAPMI_DR->PAPMI_RowId,
CASE WHEN OBS_Item_DR = '157' THEN OBS_Value ELSE NULL END as a,
CASE WHEN OBSAT_ItemAttribute_DR = '157||2' THEN OBSAT_ValueDisplay ELSE NULL END as b,
CASE WHEN OBSAT_ItemAttribute_DR = '157||4' THEN OBSAT_ValueDisplay ELSE NULL END as c,
CASE WHEN OBSAT_ItemAttribute_DR = '157||6' THEN OBSAT_ValueDisplay ELSE NULL END as d
FROM SQLUser.MR_ObservationsAttribute
Join SQLUser.MR_Observations on OBS_RowId = OBSAT_ParRef
Join SQLUser.MRC_ObservationItemAttribute on ATTR_RowId = OBSAT_ItemAttribute_DR
) temp
where OBSAT_ParRef ='75||300'
group by OBS_ParRef, OBSAT_ParRef
and what I want is No_Urut should be 1, only 67 not 67,67,67
Thank you for your help
Steve Henry
2 Replies
steve_henry2023 , I can't test your code without having your data.
And what is LIST for a function, it's not a MS SQL Server Transact-SQL function?
Please post table design as DDL, some sample data as DML statement and the expected result.
Hy olafhelper
Thank you for your help
but It's funny I think, I just try to put distinct here
List (Distinct(a)) as No_Urut
and it works
But I'm still testing, is there any impact with another query ?
Thank You
Best Regards,
Steven Henry
