March 29, 2007 at 8:43 am
If you are using SQL Server 2000, why do you post a question in the SQL Server 2005 forum?
N 56°04'39.16"
E 12°55'05.25"
March 29, 2007 at 8:48 am
SELECT
t1.PART_ID,
t1.DATE,
t1.PART_CD,
t1.REN_CD,
t1.[STATE],
t1.BN_NO,
t1.BN_CD,
t1.BN_DS,
t1.[DESC],
t1.ID
FROM Table1 AS t1
WHERE 0 = (SELECT COUNT(*) FROM Table1 AS t2 WHERE t2.PART_ID = t1.PART_ID AND t2.PART_CD < t1.PART_CD)
N 56°04'39.16"
E 12°55'05.25"
March 29, 2007 at 9:13 am
Hi Peter,
Thanks. Sorry for posting here. I tried this but still getting the duplicates.
Thanks
San.
March 29, 2007 at 9:22 am
I don't get duplicates with this
-- Prepare sample data
DECLARE
@Sample TABLE (PART_ID INT, DATE DATETIME, PART_CD VARCHAR(3), REN_CD INT, [STATE] VARCHAR(2), BN_NO INT, BN_CD VARCHAR(3), BN_DS VARCHAR(5), [DESC] VARCHAR(10), ID INT)
INSERT
@Sample
SELECT
206544, '2006-01-01', 'DDD', 11, 'TX', 45789, 'TIN', 'OTHER', NULL, 500 UNION ALL
SELECT
206544, '2006-01-01', 'DEA', 11, 'TX', 45789, 'TIN', 'OTHER', NULL, 500 UNION ALL
SELECT
206643, '2006-01-01', 'PLI', 10, 'NJ', 3300, 'LPA', NULL, NULL, 700 UNION ALL
SELECT
206643, '2006-01-01', 'TEA', 10, 'NJ', 3300, 'LPA', NULL, NULL, 700
-- Show the expected output
SELECT
t1.PART_ID,
t1
.DATE,
t1
.PART_CD,
t1
.REN_CD,
t1
.[STATE],
t1
.BN_NO,
t1
.BN_CD,
t1
.BN_DS,
t1
.[DESC],
t1
.ID
FROM
@Sample AS t1
WHERE
0 = (SELECT COUNT(*) FROM @Sample AS t2 WHERE t2.PART_ID = t1.PART_ID AND t2.PART_CD < t1.PART_CD)
IF YOU STILL DO GET DUPLICATES, IT IS BECAUSE YOU HAVEN'T TOLD US YOUR BUSINESS RULES FOR WHICH RECORDS TO ELIMINATE.
WE HAVE ASKED YOU THIS FOR 4 PAGES NOW...
N 56°04'39.16"
E 12°55'05.25"
March 29, 2007 at 9:58 am
Thanks Peter. let me try again. Thanks again.
San.
March 29, 2007 at 11:02 am
Hi Peter,
I tried the following
SELECT t1.PART_ID,t1.DATE,t1.PART_CD,t1.REN_CD,t1.[STATE],t1.BN_NO,
t1
.BN_CD,t1.BN_DS,t1.[DESC],t1.IDFROM @Sample AS t1
WHERE
0 = (SELECT COUNT(*) FROM @Sample AS t2 WHERE t2.PART_ID = t1.PART_ID AND (t2.PART_CD < t1.PART_CD OR t2.DATE < t1.DATE
OR t2.REN_CD < t1.REN_CD))
I am doing this because the other three are part of PK and if I dont do then I am getting less records than expected.
It works but Its eliminating one record from the count.
Thanks
San.
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply