Help reqd for eliminating a similar IDs- Query

  • Hi all,

    I need a basic query based on the below conditions

    Sample records

    ID BatchID Status

    1 121 COMPLETE

    2 121 PENDING

    3 121 REWORK

    4 122 ACK

    5 123 TBK

    Reqd Output

    ID BatchID Status

    4 122 ACK

    5 123 TBK

    If the Status = 'REWORK", I don't want the Particular batchID to the output.

    Kindly help me to write the query.

    thanks & regards

    Saravanakumar.R

  • select * from myTable where BatchID not in (select BatchID from myTable where Status = 'Rework')

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • Thanks for your reply,

    I did the same, but the performance of the query is not really good, it taking such a long time to get the result, and every row the query touches twice.

    is it possible to get same thing with better result using CTE / derived queries ?

    thanks & regards

    Saravanakumar.R

  • Here are two more options:

    [font="Courier New"]SELECT DISTINCT

    T1.*

    FROM

    myTable T1

    LEFT JOIN myTable T2 ON T1.BatchID = T2.BatchID

    AND T2.Status = 'Rework'

    WHERE

    T2.BatchID IS NULL

    SELECT

    T1.*

    FROM

    myTable T1

    WHERE

    NOT EXISTS (SELECT * FROM myTable T2 WHERE T2.Status = 'Rework' AND T2.BatchID = T1.BatchID)[/font]

    The first should outperform the second if your indexing is correct.

  • select * from table1 where id not in (select id from table1 where status='rework')

  • Thanks friend,

    The mentioned query does not meet the requirement.

    If we run the query BATCH ID 121 and the ID 1 & 2 are shown which is not required.

    regards

    Saravanakumar.R

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

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