Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help reqd for eliminating a similar IDs- Query Expand / Collapse
Author
Message
Posted Thursday, April 24, 2008 4:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, April 24, 2008 4:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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
Posted Thursday, April 24, 2008 4:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, April 24, 2008 8:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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
Posted Friday, April 25, 2008 3:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 04, 2013 5:13 AM
Points: 174, Visits: 55
select * from table1 where id not in (select id from table1 where status='rework')
Post #490475
Posted Friday, April 25, 2008 3:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 »

Add to briefcase

Permissions Expand / Collapse