How to make, that values ​​would not repeat at sampling? distinct does not help.

Request

select distinct ot.code, cp.Description, ml.Phone from dwh.dim.dimCatalogCounterparties cp with (nolock) join markets.dbo.otkreplen ot with (nolock) on ot.code = cp.Code collate SQL_Latin1_General_CP1_CI_AS join dwh.dm.CounterpartySegmentMainLink_Arc ml with (nolock) on ml.iCounterpartyID = cp.iCounterpartiesID

Result

 code Description Phone 101-00003362 ПП Школьний Дмитрий 380663984155 101-00003362 ПП Школьний Дмитрий 380675636350 101-00003362 ПП Школьний Дмитрий 380675636354 101-00003362 ПП Школьний Дмитрий 380675729293 101-00003362 ПП Школьний Дмитрий 380973470838 101-00004499 ГЕДЕОН ДТ 380509107240 101-00004499 ГЕДЕОН ДТ 380542658701
  • one
    Explain what means distinct does not help? Does it not work at all or does it return something wrong? (preferably with examples of what it returns and what you think should be) ps distinct refers to the whole request and not just to the first field. - Batanichek
  • returns duplicate data with repetition, code, description, phone. code, zapdeytil. - Niflungar
  • four
    Well, the phone is different. distinct applies to all fields - Batanichek
  • and how can I get data that will not be repeated? - Niflungar
  • 2
    if the “PP School Dmitry” includes 5 phone numbers - which one to show? distinct works correctly - shows all the requested combinations, all different, unique. If the phone is not interesting - then remove it from the list of displayed fields, or as an answer given by Batanichek - show one of them - ale

2 answers 2

You can still like this:

 SELECT DISTINCT ot.code, cp.[Description], STUFF((SELECT DISTINCT '; ' + ml.Phone FROM dwh.dm.CounterpartySegmentMainLink_Arc ml with (nolock) WHERE ml.iCounterpartyID = cp.iCounterpartiesID FOR XML PATH('')),1,2,'') as phones FROM dwh.dim.dimCatalogCounterparties cp with (nolock) INNER JOIN markets.dbo.otkreplen ot with (nolock) on ot.code = cp.Code collate SQL_Latin1_General_CP1_CI_AS

At the exit:

 code Description phones ------------ ------------------- -------------------------------------------------------------------- 101-00003362 ПП Школьний Дмитрий 380663984155; 380675636350; 380675636354; 380675729293; 380973470838 101-00004499 ГЕДЕОН ДТ 380509107240; 380542658701 (2 row(s) affected)

    I think so

     select ot.code, cp.Description, max(ml.Phone) Phone from dwh.dim.dimCatalogCounterparties cp with (nolock) join markets.dbo.otkreplen ot with (nolock) on ot.code = cp.Code collate SQL_Latin1_General_CP1_CI_AS join dwh.dm.CounterpartySegmentMainLink_Arc ml with (nolock) on ml.iCounterpartyID = cp.iCounterpartiesID group by ot.code,cp.Description