Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Jawad
Starting Member
17 Posts |
Posted - 2012-08-06 : 14:54:28
|
HiSQL Server 2005Data loads in my table every day with a new loaded(last column) as follows.CREATE TABLE T1(colId int,col2 varchar(10),col3 varchar(10),col4 varchar(10),colLoadId int);INSERT INTO t1 VALUES (6,'abc','pqr','xyz',1);INSERT INTO t1 VALUES (7,'abc','pqt','xzy',1);INSERT INTO t1 VALUES (6,'abc','pqr','xyz',2);INSERT INTO t1 VALUES (7,'abc','pqr','xyz',2);SELECT * FROM t1;colId col2 col3 col4 colLoadId6 abc pqr xyz 17 abc pqt xzy 16 abc pqr xyz 27 abc pqr xyz 2 On the base columns (ColId and ColLoadId) and I need to find which ColId's date change in this load.I do it like follows:SELECT t.Colid ,t.Col2 ,t.Col3 ,t.Col4 FROM t1 t JOIN t1 t2 ON (t.colId=t2.colId) WHERE t.colLoadId=1 AND t2.colLoadId=2 AND (t.Col2<>t2.Col2 OR t.Col3<>t2.Col3 OR t.Col4<>t2.Col4) It serves my purpose but in actual my table got 30+ columns to compare for every record.I feel adding 30+ 'OR' clauses to compare every row is not the best way to do it.So can anyone guide me a more efficient way to compare rows on the base of (ColId and ColLoadId)?Wishes |
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-08-06 : 15:12:04
|
SELECT Colid,Col2,Col3,Col4 FROM t1 WHERE colLoadId=1EXCEPTSELECT Colid,Col2,Col3,Col4 FROM t1 WHERE colLoadId=2 |
|
|
Jawad
Starting Member
17 Posts |
Posted - 2012-08-06 : 15:23:46
|
Thanks robvolk |
|
|
Jawad
Starting Member
17 Posts |
Posted - 2012-10-02 : 08:25:05
|
quote: Originally posted by robvolk SELECT Colid,Col2,Col3,Col4 FROM t1 WHERE colLoadId=1EXCEPTSELECT Colid,Col2,Col3,Col4 FROM t1 WHERE colLoadId=2
Hi robvolkyou solutions is exactly what I want.Now I wish to go one step farther in performance enhansement and need your help again please.I am doing it like follows:SELECT COUNT(*) FROM ( SELECT Colid,Col2,Col3,Col4 FROM t1 WHERE colLoadId=1 EXCEPT SELECT Colid,Col2,Col3,Col4 FROM t1 WHERE colLoadId=2 ) but it compare all rows all the times, I wish that it exit at 1st row with the difference and give me output of 1 instead of all row comparison.Wishes |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-02 : 09:37:08
|
The following may be faster if you have lot of differences. If there are only one or two rows that qualify then they are likely to perform the same.IF EXISTS ( SELECT Colid,Col2,Col3,Col4 FROM t1 WHERE colLoadId=1 EXCEPT SELECT Colid,Col2,Col3,Col4 FROM t1 WHERE colLoadId=2 ) SELECT 1ELSE SELECT 0; |
|
|
Jawad
Starting Member
17 Posts |
Posted - 2012-10-02 : 09:48:59
|
quote: Originally posted by sunitabeck The following may be faster if you have lot of differences. If there are only one or two rows that qualify then they are likely to perform the same.IF EXISTS ( SELECT Colid,Col2,Col3,Col4 FROM t1 WHERE colLoadId=1 EXCEPT SELECT Colid,Col2,Col3,Col4 FROM t1 WHERE colLoadId=2 ) SELECT 1ELSE SELECT 0;
Thanks a lot sunitabeck |
|
|
|
|
|
|
|