Home Forums SQL Server 2008 SQL Server Newbies Using Count Distinct in a CASE statement to choose more than one unique record RE: Using Count Distinct in a CASE statement to choose more than one unique record

  • monkeyflavours (8/12/2014)


    Hopefully this is a quick question....

    I’m scratching my head with a bit of script. Why would the below choose a unique set of records from one table to put into another table? I can’t get over the =’1’ part. In my thinking it would only enter one record (rather than a set). How would it count the next unique record as ‘1’ (adding more than 1 contractid to a contract table)?

    SELECT

    CASE

    WHEN COUNT(DISTINCT(Contract_ID)) = '1' THEN MAX(Contract_ID)

    ELSE '-1'

    END AS Contract_ID,

    Cheers

    Chris

    Not the only problem but are you really comparing an integer (Count) to a character '1'?