Home Forums SQL Server 2008 T-SQL (SS2K8) Extracting Data from single table RE: Extracting Data from single table

  • October 27, 2012 at 11:22 pm

    #1553514

    vinu512 (10/18/2012)

    This is another way of doing it...and I guess the simplest way....:-)

    With YOURTABLE (TICKET,STATUS)

    AS

    (

    SELECT 9543,1 UNION ALL

    SELECT 9543,5 UNION ALL

    SELECT 9543,5 UNION ALL

    SELECT 9544,1 UNION ALL

    SELECT 9546,1 UNION ALL

    SELECT 9547,1 UNION ALL

    SELECT 9547,5

    )

    Select * From YOURTABLE

    Where TICKET NOT IN (Select TICKET From YOURTABLE Where STATUS = 5)

    GO

    It's a good idea for the data given. I have a hard time believing that they have no statuses between 1 and 5, though. If they do, the code you posted would produce duplicate ticket numbers. Of course, that may be just what the doctor ordered if there are status other than 1 and 5.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)