Post reply

sql problem

  • December 4, 2009 at 1:05 am

    #135793

    Hi i have following table (very big table)

    columns

    ID , CODE ,...............................

    values

    1 , X , ..........

    1, X ,...........

    2, A,............

    2, A,............

    3, A,............

    3, A,............

    4, C,............

    4, D,............

    .

    .

    .

    i need to find out all rows which are having more than one value for each ID

    example 4 is having C and D

    can any one help

    Thanks

    Salil

  • December 4, 2009 at 1:19 am

    #1087521

    untested, but it should work:

    ;WITH cte AS

    (

    SELECT ID, CODE FROM MyTable GROUP BY ID, CODE

    )

    SELECT ID, COUNT(*) AS CNT

    FROM CTE

    GROUP BY ID

    HAVING COUNT(*) > 1


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • December 4, 2009 at 1:39 am

    #1087525

    Another way:

    ;WITH cte AS

    (

    SELECT ID

    ,dense_rank() over ( partition by ID order by ID,) AS R

    FROM MyTable

    )

    SELECT ID

    FROM cte

    WHERE R > 1

    GROUP BY ID

    -Vikas Bindra

  • December 4, 2009 at 2:03 am

    #1087532

    SELECT ID

    FROM MyTable

    GROUP BY ID

    HAVING COUNT(DISTINCT CODE)>1

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • December 4, 2009 at 2:33 am

    #1087539

    SELECT *

    FROM very big table tblo

    WHERE EXISTS

    (

    SELECT 1

    FROM very big table tbln

    WHERE tbln.id <> tblo.id

    AND tbln.code = tblo.code

    )

    OR EXISTS

    (

    SELECT 1

    FROM very big table tbla

    WHERE tbla.id = tblo.id

    AND tbla.code <> tblo.code

    )

  • December 4, 2009 at 3:06 am

    #1087552

    thanks for help query is working fine

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply