none
If duplicate column data found show atleast one value

    Question

  • Hi,

    I want to show atleast one column data value from the duplicates columns data.  I took the following one of thread before but this time the requirement is little bit different.

    For example;

    Declare @myTable Table (City Varchar(20), code Int) 
    Insert Into @myTable 
    Select 'Chicago', 501 Union All 
    Select 'New Jersy', 601 Union All 
    Select 'New Jersy', 602 Union All 
    Select 'New Jersy', 603 Union All 
    Select 'Paris', 101 Union All 
    Select 'Texas', 901
    
    Select * From @myTable As MainQry Where Exists (Select 1 From @myTable As SubQry Where SubQry.City = MainQry.City Group By SubQry.City Having COUNT(*) =  1)
    
    
    --output
    City                 code
    -------------------- -----------
    Chicago              501
    Paris                101
    Texas                901
    
    
    --------------------------------------------------------------------------------
    

    Now i need the output in the following format;

    City                 code
    -------------------- -----------
    Chicago              501
    Paris                101
    Texas                901
    NewJersy         (Any code)

    NewJersy appears duplicates even though the code is different. I want to show atleast one value from the duplicate.

    Thanks.
     

     

     

     

     

     

     

    Friday, October 07, 2011 7:04 PM

Answers

  • If that's all you need and you don't care about code, try

     

    select City, max(Code) as Code from dbo.CitiesInfo
    
    GROUP BY City
    
    ORDER BY City
    

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Kashif Chotu Friday, October 07, 2011 7:14 PM
    Friday, October 07, 2011 7:06 PM
    Moderator

All replies

  • If that's all you need and you don't care about code, try

     

    select City, max(Code) as Code from dbo.CitiesInfo
    
    GROUP BY City
    
    ORDER BY City
    

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Kashif Chotu Friday, October 07, 2011 7:14 PM
    Friday, October 07, 2011 7:06 PM
    Moderator
  • Thanks.
    Friday, October 07, 2011 7:14 PM