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

  • October 27, 2012 at 11:18 pm

    #1553513

    drew.allen (10/15/2012)

    I think that a GROUP BY is much simpler and probably faster, too.

    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 TICKET, MAX(STATUS) AS STATUS

    FROM YOURTABLE

    GROUP BY TICKET

    HAVING MAX(STATUS) = 1

    GO

    Drew

    Oh, be careful. For this particular data, that will absolutely work. If they introduce status less than 5 that aren't cause for rejection, it won't.

    --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)