optimization challenge

Well i wanted to prove to some guys that cursors are not really that important:shocked: .
:D So this code is suppose to remove duplicate tuples from a table without temporary tables or cursors:D. Except it needs some optimization(and alot of system down time, not sure about that:confused: ).
I would like it, if some one could find an instance of the table when the below code fails or some way to optimize the code or anything;) .

--trashtable for real data
create table abc
(col1 tinyint,
col2 tinyint,
col3 tinyint)

--trash values for trash table
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (2,2,2)
insert into abc values (2,2,2)
insert into abc values (2,2,2)
insert into abc values (3,2,1)
insert into abc values (2,2,3)
insert into abc values (3,2,4)

--check that there are ten rows
select * from abc
--check that there are only five distinct rows
select distinct * from abc

--run code : next 15 line as a batch
declare @.lp tinyint
declare @.col1 tinyint,@.col2 tinyint,@.col3 tinyint
set @.lp=1
while @.lp>0
begin
if not exists (select top 1 * from abc group by col1,col2,col3 having count(col1)>1)
set @.lp=0
else
begin
select top 1 @.col1 = col1,@.col2 = col2,@.col3 = col3 from abc group by col1,col2,col3 having count(col1)>1
delete from abc where col1=@.col1 and col2=@.col2 and col3=@.col3
insert into abc values(@.col1,@.col2,@.col3)
end
end

--only distinct values left in trash table
select * from abc

--think code can be optimized
--just wanted to prove: can be done without cursors or temporary tablesI know this is a cheat and I'm not exactly rising to the challenge however there is a pretty good discussion about (and links to) removing dupes here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256

HTH|||Thanks:cool:

Had a look at the URL.
another cool way is to use SET ROWCOUNT with DELETE.|||While I realize that this uses a temp table, the usage is quite small and this is pretty efficient:
--trashtable for real data
create table abc
( col1 tinyint
, col2 tinyint
, col3 tinyint)

--trash values for trash table
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (1,1,1)
insert into abc values (2,2,2)
insert into abc values (2,2,2)
insert into abc values (2,2,2)
insert into abc values (3,2,1)
insert into abc values (2,2,3)
insert into abc values (3,2,4)

--check that there are ten rows
select * from abc

--check that there are only five distinct rows
select distinct * from abc

create table ptp_dupes
( col1 tinyint
, col2 tinyint
, col3 tinyint)

INSERT INTO ptp_dupes (col1, col2, col3)
SELECT col1, col2, col3
FROM abc
GROUP BY col1, col2, col3
HAVING 1 < Count(*)

BEGIN TRANSACTION

DELETE FROM abc
WHERE EXISTS (SELECT *
FROM ptp_dupes
WHERE ptp_dupes.col1 = abc.col1
AND ptp_dupes.col2 = abc.col2
AND ptp_dupes.col3 = abc.col3)

INSERT INTO abc (col1, col2, col3)
SELECT col1, col2, col3
FROM ptp_dupes

COMMIT TRANSACTION

SELECT col1, col2, col3 FROM abc ORDER BY col1, col2, col3

SELECT DISTINCT col1, col2, col3 FROM abc ORDER BY col1, col2, col3Unfortunately, there isn't anything I can recommend as even close to efficient that doesn't use a temp table at all. The cursor solutions are inefficient, and the code that you've shown is interesting, but not very efficient.

-PatP

operator precedence challenge

I'm having problems with the query below. It works fine until the "NOT IN" part and I'm not sure why. Basically, I'm getting records where their educ_audio field is set to "no". I've messed with the parens to try to force SQL to process the NOT IN part before the other clauses without avail. Can someone shed some light?

TIA

SELECT distinct contacts.fname, contacts.lname, contacts.company, contacts.contact_id, contacts.business_phone, contacts.emailAddress, contacts.dateLastContact FROM journal INNER JOIN contacts ON journal.contact_id = contacts.contact_id INNER JOIN products ON journal.product_code = products.product_code WHERE ( journal.product_code IN ('ABLE') ) OR (( journal.product_code IN ('JOBS') ) AND ( journal.product_status IN ('12','14','15') )) OR (( products.prod_design IN ('audio') ) AND ( products.library_code IN ('hrss') )) AND (journal.journal_id NOT IN (SELECT journal.journal_id FROM journal INNER JOIN contacts ON journal.contact_id = contacts.contact_id INNER JOIN products ON products.product_code = journal.product_code where ( contacts.educ_audio IN ('no') ) )) ORDER BY contacts.lname asc, contacts.fnameI think you can get the results you want by enclosing all your OR clauses in one set of parenthesis. If I understand correctly, your records must satisfy at least one of the OR clauses, and the AND clause:

SELECT distinct
contacts.fname,
contacts.lname,
contacts.company,
contacts.contact_id,
contacts.business_phone,
contacts.emailAddress,
contacts.dateLastContact
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON journal.product_code = products.product_code
WHERE ((journal.product_code IN ('ABLE'))
OR ((journal.product_code IN ('JOBS')) AND (journal.product_status IN ('12','14','15')))
OR ((products.prod_design IN ('audio')) AND (products.library_code IN ('hrss'))))
AND (journal.journal_id NOT IN
(SELECT journal.journal_id
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON products.product_code = journal.product_code
WHERE (contacts.educ_audio IN ('no'))))
ORDER BY contacts.lname asc,
contacts.fname

But you can clean this up a lot more
First, IN ('ABLE') is equivalent to ='ABLE', so don't muddy the waters with more parentheses than you need.
Second, many of your Parenthesis pairs are superfluous, in that they enclose only one clause. Eliminate the clutter.

SELECT distinct
contacts.fname,
contacts.lname,
contacts.company,
contacts.contact_id,
contacts.business_phone,
contacts.emailAddress,
contacts.dateLastContact
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON journal.product_code = products.product_code
WHERE (journal.product_code = 'ABLE'
OR (journal.product_code = 'JOBS' AND journal.product_status IN ('12','14','15'))
OR (products.prod_design = 'audio' AND products.library_code IN ('hrss')))
AND journal.journal_id NOT IN
(SELECT journal.journal_id
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON products.product_code = journal.product_code
WHERE contacts.educ_audio = 'no')
ORDER BY contacts.lname asc,
contacts.fname

--Lastly, consider converting you NOT IN clause to a LEFT OUTER JOIN subquery:

SELECT distinct
contacts.fname,
contacts.lname,
contacts.company,
contacts.contact_id,
contacts.business_phone,
contacts.emailAddress,
contacts.dateLastContact
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON journal.product_code = products.product_code
LEFT OUTER JOIN
(SELECT journal.journal_id
FROM journal
INNER JOIN contacts ON journal.contact_id = contacts.contact_id
INNER JOIN products ON products.product_code = journal.product_code
WHERE contacts.educ_audio = 'no') ExcludeRecords
on journal.journal_id = ExludeRecords.journal_id
WHERE (journal.product_code = 'ABLE'
OR (journal.product_code = 'JOBS' AND journal.product_status IN ('12','14','15'))
OR (products.prod_design = 'audio' AND products.library_code IN ('hrss')))
AND ExcludeRecords.journal_id is null
ORDER BY contacts.lname asc,
contacts.fname

If you take the time to develop a coding style that includes neat and consistent indenting and formatting, you will be rewarded with much clearer and more bug-free code.|||Thanks for your help.

Reason the SQL looks like it does (not indented, etc) is because that was a paste from my application. This is for an ad-hoc query tool I'm building.

As to your suggestions, they all seem to work except the last one.
Error: The column prefix 'ExludeRecords' does not match with a table name or alias name used in the query.|||Just a typo.

on journal.journal_id = ExludeRecords.journal_id

...should have been:

on journal.journal_id = ExcludeRecords.journal_id