Help reqd for eliminating a similar IDs- Query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Help reqd for eliminating a similar IDs-...

Help reqd for eliminating a similar IDs- Query

Rate Topic

Display Mode

Topic Options

Author

Message

Saravanakumar.Rathinam

Posted Thursday, April 24, 2008 4:10 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2008 1:05 AM
Points: 49, Visits: 493

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

Post #489765

RyanRandall

Posted Thursday, April 24, 2008 4:26 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652

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.

Post #489779

Saravanakumar.Rathinam

Posted Thursday, April 24, 2008 4:58 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2008 1:05 AM
Points: 49, Visits: 493

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

Post #489795

Michael Earl-395764

Posted Thursday, April 24, 2008 8:37 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

Here are two more options:

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)

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

Post #489985

sameyullah

Posted Friday, April 25, 2008 3:11 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 21, 2013 5:22 AM
Points: 174, Visits: 54

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

Post #490475

Saravanakumar.Rathinam

Posted Friday, April 25, 2008 3:31 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2008 1:05 AM
Points: 49, Visits: 493

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

Post #490488

« Prev Topic | Next Topic »

Permissions