SQL SERVER – Delete Duplicate Records – Rows

March 1, 2007

Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3.

DELETE FROM MyTable WHERE ID NOT IN ( SELECT MAX(ID) FROM MyTable GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

Watch the view to see the above concept in action:

[youtube=http://www.youtube.com/watch?v=ioDJ0xVOHDY]

Reference : Pinal Dave (https://blog.sqlauthority.com)

Duplicate Records, SQL Scripts

SQL SERVER – T-SQL Script to find the CD key from Registry

SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation

SQL SERVER Agent Missing from SSMS

September 29, 2021

SQL SERVER – Statistics for Read-Only Database are in TempDB

November 29, 2013

How to Find SQL Server Deprecated Features Used by the Application? – Interview Question of the Week #165

March 18, 2018

450 Comments. Leave new

Saravanan
October 10, 2008 7:38 pm
Dear All,
I forgot to tell you one thing. I got a column which stores created date in that table.
Cheers,
Saravanan
Reply
R.Rohini
October 12, 2008 5:57 pm
i like 2 learn more about the sql queries ,so please send me any updated information about the sql.
Reply
Mxolisi Phiri
October 24, 2008 5:27 pm
Good coding you guys have displayed. I have just added a while loop to eliminate all duplicates.
–create fruit table and populate it with duplicate fruits
CREATE TABLE #tblFruit
(
ID int identity,
Fruit varchar(7)
)
INSERT INTO #tblFruit
VALUES(‘Banana’)
INSERT INTO #tblFruit
VALUES(‘Banana’)
INSERT INTO #tblFruit
VALUES(‘Pear’)
INSERT INTO #tblFruit
VALUES(‘Orange’)
INSERT INTO #tblFruit
VALUES(‘Orange’)
INSERT INTO #tblFruit
VALUES(‘Apple’)
INSERT INTO #tblFruit
VALUES(‘Banana’)
INSERT INTO #tblFruit
VALUES(‘Apple’)
–display all the fruits you have, and count how many duplicates you have to see if your code works
SELECT * FROM #tblFruit
SELECT COUNT(FRUIT) FROM #tblFruit
SELECT COUNT(DISTINCT(FRUIT)) FROM #tblFruit
–loop through your table and delete any duplicates, display your new table w/o duplicates, count if you still have duplicates or not, delete your temp table
WHILE EXISTS
(
SELECT MIN(ID)
FROM #tblFruit
GROUP BY FRUIT
HAVING COUNT(FRUIT) > 1
)
BEGIN
DELETE
FROM #tblFruit
WHERE ID IN ( SELECT MIN(ID) FROM #tblFruit
GROUP BY Fruit
HAVING COUNT(Fruit) > 1
)
SELECT * FROM #tblFruit
SELECT COUNT(FRUIT) FROM #tblFruit
SELECT COUNT(DISTINCT(FRUIT)) FROM #tblFruit
DROP TABLE #tblFruit
END
Reply
Pavan Thakur
November 13, 2008 1:00 pm
Thanks Vamshi…It helped me a lot
Reply
Ashwin Rau
November 19, 2008 9:15 pm
Pinal,
Thanks for the excellent web site. Just wanted to point out a small typo.
You have group by DuplicateValueColumn2 twice. It should be DuplicateValueColumn3. I know most people would catch it. Just thought you should update the web site solution with correct code. Thanks.
Ash
Reply
Ravichandra
November 20, 2008 3:03 pm
Hi ;
I have two queries which is below. I want to get the records which are excess in Region1.table don’t want display common records also
1). SELECT * FROM
Region1.table
WHERE POST_KNTNR = ‘6731’
AND SLSKB_INIT = ‘NL’
AND VAKD = ‘DKK’
AND year(MDTGL_TMSTM) = 2008
2). SELECT * FROM
Region2.table
WHERE POST_KNTNR = ‘6731’
AND SLSKB_INIT = ‘NL’
AND VAKD = ‘DKK’
AND year(MDTGL_TMSTM) = 2008
Reply
Srinivas Asapu
November 21, 2008 12:18 pm
Hi Ravichandra,
Use the following code. Replace COLUMN_LIST with column names. Let me know if you have any issues.
SELECT COLUMN_LIST FROM (SELECT DISTINCT ‘U’ AS SETNAME,
* FROM REGION1.TABLE WHERE POST_KNTNR = ‘6731’
AND SLSKB_INIT = ‘NL’
AND VAKD = ‘DKK’
AND YEAR (MDTGL_TMSTM) = 2008
UNION ALL
SELECT DISTINCT NULL, * FROM
REGION2.TABLE
WHERE POST_KNTNR = ‘6731’
AND SLSKB_INIT = ‘NL’
AND VAKD = ‘DKK’
AND YEAR (MDTGL_TMSTM) = 2008) A
GROUP BY COLUMN_LIST
HAVING COUNT (*) = 1 AND MAX (SETNAME) = ‘U’
Reply
GArun
December 4, 2008 10:52 am
Hi,
A table contains PRIMARY KEY CLUSTERED and it contains 20 partitions. How to delete a FIRST (1st) Partition.
Thanks
GAG
Reply
Ramesh sharma
December 9, 2008 7:27 pm
hi sir
i m very impressed with your article ?
it is great sir
Reply
Asha Sharda
December 23, 2008 3:14 pm
To delete the DuplicateRows when the ID column is UniqueIdentifier
DELETE
FROM MyTable where Id not in(
SELECT Top 1 b.Id
FROM MyTable as b
WHERE b.Col1= MyTable.Col1
AND b.Col2= MyTable.Col2
AND b.Col3= MyTable.Col3
)
Reply
Srinivas Madabushi
December 29, 2008 9:30 pm
Hi,
Do the following steps:
1: Create a temp table with the same column names as the main table
2: Copy the duplicate records by using count>1 in the where clause
3: Delete the duplicate records in the main table using the count>1
4: Insert the records from the temp table to the original table
5: Clear the temp table
Reply
Prajot
January 21, 2009 3:58 pm
If in the sql server table there is no primary key in the table and the data is huge and we want to delete the duplicate records from that data by using the query so tell me which is the feasible way for deleting duplicate records.
Reply
dyamond
January 31, 2009 10:46 am
Please help with the following:
SELECT
date,
name,
desc,
‘Code’ as cdDesc
FROM activity
WHERE
date = ‘2009-01-05′ and
site = ’80’ and
exists
(select code
from activity
where code in (‘55544′,’33333’, ‘66666’)
GROUP BY code HAVING COUNT(*) > 2)
UNION ALL
SELECT
date,
name,
desc,
‘Code2’ as cdDesc
FROM activity
WHERE
date = ‘2009-01-05′ and
site = ’80’ and
exists
(select code
from activity
where code in (‘2222′,’3333’, ‘66666’, ‘88888’)
GROUP BY code HAVING COUNT (*) > 3)
The issue is that some code(s) exist in multiple rows and I only need the whole group to display. I used “AND’ but that gave me no results. ANY HELP would be appreciated.
Reply
qute
January 31, 2009 7:14 pm
CREATE TABLE #temp
(SAP_FL varchar(40),
EQUIPMENT_NO varchar(18),
SHORT_DESCR varchar(100),
COMPL_DATE datetime,
NOTIFICATION_NO varchar(12),
ACTIVITY_CODE VARCHAR(10),
ACTIVITY_NAME VARCHAR(100),
TOTAL_COUNT float,
CONST_TYPE varchar(100),
UNIT varchar(50),
PriorityID int,
)
————————————————————-
insert into #temp values ( ‘1023-A615400-443401-00901-00401’ ,50001564 , ‘Ultrasonic stack #30 ‘ , ’11/14/2005’ , ‘500722482’ , ‘REPL’ , ‘Replaced’ , 70628 , 80159257 , ‘EA’ , 2 )
insert into #temp values ( ‘1023-A614400-443401-00901-00501’ ,50001564 , ‘Ultrasonic stack #30 ‘ , ‘4/15/2005’ , ‘500357639’ , ‘REPL’ , ‘Replaced’ , 70404 , 80159257 , ‘EA’ , 3 )
insert into #temp values ( ‘1023-A615400-443401-00901-00201’ ,50001564 , ‘Ultrasonic stack #30 ‘ , ‘3/21/2005’ , ‘500329094’ , ‘REPL’ , ‘Repaired’ , 70321 , 80159257 , ‘EA’ , 2 )
insert into #temp values ( ‘1023-F306400-411401-00801′ ,’50001721’, ‘Module- Cutting Fluff Ovrnite/wing 295mm’, ’10/4/2008′ ,’502737991′,’REPR’, ‘Repaired’, 218242 ,’80296971′ ,’EA’, 3)
insert into #temp values ( ‘1023-F306400-411401-00801′ ,’50001721’, ‘Module- Cutting Fluff Ovrnite/wing 295mm’, ‘8/29/2008′ ,’502675451′,’REPR’, ‘Repaired’, 218242 ,’80296971′ ,’EA’, 3)
insert into #temp values ( ‘1023-F306400-411401-00801′ ,’50001721’, ‘Module- Cutting Fluff Ovrnite/wing 295mm’, ‘8/29/2008′ ,’502620150′,’REPR’, ‘Repaired’, 218242 ,’80296971′ ,’EA’, 3)
insert into #temp values ( ‘1023-F306400-411401-00801′ ,’50001721’, ‘Module- Cutting Fluff Ovrnite/wing 295mm’, ‘6/18/2008′ ,’502495333′,’REPR’, ‘Repaired’, 208032 ,’80296971′ ,’EA’, 3)
insert into #temp values ( ‘1023-F306400-411401-00801′ ,’50001721’, ‘Module- Cutting Fluff Ovrnite/wing 295mm’, ‘9/7/2007′ ,’501944796′,’REPR’, ‘Repaired’, 197999 ,’80296971′ ,’EA’, 3)
insert into #temp values ( ‘1023-F306400-411401-00801′ ,’50001721’, ‘Module- Cutting Fluff Ovrnite/wing 295mm’, ‘9/23/2005′ ,’500648350′,’REPR’, ‘Repaired’, 159277 ,’80296971′ ,’EA’, 3)
insert into #temp values ( ‘1023-F306400-411401-00801′ ,’50001721’, ‘Module- Cutting Fluff Ovrnite/wing 295mm’, ’12/17/2004′,’500182407′,’REPR’, ‘Repaired’, 118935 ,’80296971′ ,’EA’, 3)
—————————————————-
select
t1.SAP_FL as SAP_FL_ST ,
t2.SAP_FL as SAP_FL_ED,
t1.EQUIPMENT_NO as EQUIPMENT_NO_ST,
t2.EQUIPMENT_NO as EQUIPMENT_NO_ED,
t1.COMPL_DATE as COMPL_DATE_ST ,
t2.COMPL_DATE as COMPL_DATE_ED,
t1.SHORT_DESCR as SHORT_DESCR_ST,
t2.SHORT_DESCR as SHORT_DESCR_ED,
t1.NOTIFICATION_NO as NOTIFICATION_NO_ST,
t2.NOTIFICATION_NO as NOTIFICATION_NO_ED,
t1.ACTIVITY_CODE as ACTIVITY_CODE_ST,
t2.ACTIVITY_CODE as ACTIVITY_CODE_ED ,
t1.ACTIVITY_NAME as ACTIVITY_NAME_ST ,
t2.ACTIVITY_NAME as ACTIVITY_CODE_ED,
t1.TOTAL_COUNT as TOTAL_COUNT_ST ,
t2.TOTAL_COUNT as TOTAL_COUNT_ED ,
t1.TOTAL_COUNT –
t2.TOTAL_COUNT as TOTAL_COUNT_DIFFERENCE,
t1.CONST_TYPE as CONST_TYPE_ST ,
t2.CONST_TYPE as CONST_TYPE_ED ,
t1.UNIT
from (select SAP_FL as ‘SAP_FL’,
EQUIPMENT_NO as ‘EQUIPMENT_NO’,
SHORT_DESCR as ‘SHORT_DESCR’,
COMPL_DATE as ‘COMPL_DATE’,
NOTIFICATION_NO as ‘NOTIFICATION_NO’,
ACTIVITY_CODE as ‘ACTIVITY_CODE’,
ACTIVITY_NAME as ‘ACTIVITY_NAME’,
CONST_TYPE as ‘CONST_TYPE’,
UNIT as ‘UNIT’
,max(TOTAL_COUNT) as ‘TOTAL_COUNT’
from #temp
— where EQUIPMENT_NO = 50001721
group by SAP_FL ,
EQUIPMENT_NO ,
COMPL_DATE ,
NOTIFICATION_NO ,
ACTIVITY_CODE ,
ACTIVITY_NAME ,
SHORT_DESCR ,
CONST_TYPE ,
UNIT )t1
INNER JOIN #temp t2
ON t1.EQUIPMENT_NO=t2.EQUIPMENT_NO
AND t1.ACTIVITY_CODE=t2.ACTIVITY_CODE
AND t1.COMPL_DATE>t2.COMPL_DATE
=====================================
OUTPUT–if we considered equi no 50001721
==================================
SAP_FL_ST SAP_FL_ED EQUIPMENT_NO_ST EQUIPMENT_NO_ED COMPL_DATE_ST COMPL_DATE_ED NOTIFICATION_NO_ST NOTIFICATION_NO_ED ACTIVITY_CODE_ST ACTIVITY_CODE_ED ACTIVITY_NAME_ST ACTIVITY_NAME_ED TOTAL_COUNT_ED TOTAL_COUNT_ST TOTAL_COUNT_DIFFERENCE SHORT_DESCR_ST SHORT_DESCR_ED CONST_TYPE_ST CONST_TYPE_ED UNIT
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 10/4/2008 8/29/2008 502737991 502675451 REPR REPR Repaired Repaired 218242 218242 0 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 10/4/2008 8/29/2008 502737991 502620150 REPR REPR Repaired Repaired 218242 218242 0 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 8/29/2008 502675451 502620150 REPR REPR Repaired Repaired 218242 218242 0 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 10/4/2008 6/18/2008 502737991 502495333 REPR REPR Repaired Repaired 218242 208032 10210 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 6/18/2008 502620150 502495333 REPR REPR Repaired Repaired 218242 208032 10210 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 6/18/2008 502675451 502495333 REPR REPR Repaired Repaired 218242 208032 10210 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 10/4/2008 9/7/2007 502737991 501944796 REPR REPR Repaired Repaired 218242 197999 20243 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 6/18/2008 9/7/2007 502495333 501944796 REPR REPR Repaired Repaired 208032 197999 10033 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 9/7/2007 502620150 501944796 REPR REPR Repaired Repaired 218242 197999 20243 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 9/7/2007 502675451 501944796 REPR REPR Repaired Repaired 218242 197999 20243 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 10/4/2008 9/23/2005 502737991 500648350 REPR REPR Repaired Repaired 218242 159277 58965 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 6/18/2008 9/23/2005 502495333 500648350 REPR REPR Repaired Repaired 208032 159277 48755 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 9/23/2005 502620150 500648350 REPR REPR Repaired Repaired 218242 159277 58965 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 9/23/2005 502675451 500648350 REPR REPR Repaired Repaired 218242 159277 58965 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 9/7/2007 9/23/2005 501944796 500648350 REPR REPR Repaired Repaired 197999 159277 38722 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 10/4/2008 12/17/2004 502737991 500182407 REPR REPR Repaired Repaired 218242 118935 99307 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 6/18/2008 12/17/2004 502495333 500182407 REPR REPR Repaired Repaired 208032 118935 89097 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 12/17/2004 502620150 500182407 REPR REPR Repaired Repaired 218242 118935 99307 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 8/29/2008 12/17/2004 502675451 500182407 REPR REPR Repaired Repaired 218242 118935 99307 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 9/23/2005 12/17/2004 500648350 500182407 REPR REPR Repaired Repaired 159277 118935 40342 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-F306400-411401-00801 1023-F306400-411401-00801 50001721 50001721 9/7/2007 12/17/2004 501944796 500182407 REPR REPR Repaired Repaired 197999 118935 79064 Module, Cutting Fluff Ovrnite/wing 295mm Module, Cutting Fluff Ovrnite/wing 295mm 80296971 80296971 EA
1023-A614400-443401-00901-00501 1023-A615400-443401-00901-00201 50001564 50001564 3/21/2005 4/15/2005 500357639 500329094 REPL REPL Replaced Repaired 70404 70321 83 Ultrasonic stack #30 Ultrasonic stack #30 80159257 80159257 EA
1023-A615400-443401-00901-00401 1023-A614400-443401-00901-00501 50001564 50001564 4/15/2005 11/14/2005 500722482 500357639 REPL REPL Replaced Replaced 70628 70404 224 Ultrasonic stack #30 Ultrasonic stack #30 80159257 80159257 EA
1023-A615400-443401-00901-00401 1023-A615400-443401-00901-00201 50001564 50001564 3/21/2005 11/14/2005 500722482 500329094 REPL REPL Replaced Repaired 70628 70321 307 Ultrasonic stack #30 Ultrasonic stack #30 80159257 80159257 EA
here in output it puts tries to take start date as max date and calcuates difference based on max(date)- all remaining dates
but i want in this sequence
1023-F306400-411401-00801 50001721 Module, Cutting Fluff Ovrnite/wing 295mm 10/4/2008 502737991 REPR Repaired 218242 80296971 EA 3
1023-F306400-411401-00801 50001721 Module, Cutting Fluff Ovrnite/wing 295mm 8/29/2008 502675451 REPR Repaired 218242 80296971 EA 3
1023-F306400-411401-00801 50001721 Module, Cutting Fluff Ovrnite/wing 295mm 8/29/2008 502620150 REPR Repaired 218242 80296971 EA 3
1023-F306400-411401-00801 50001721 Module, Cutting Fluff Ovrnite/wing 295mm 6/18/2008 502495333 REPR Repaired 208032 80296971 EA 3
1023-F306400-411401-00801 50001721 Module, Cutting Fluff Ovrnite/wing 295mm 9/7/2007 501944796 REPR Repaired 197999 80296971 EA 3
1023-F306400-411401-00801 50001721 Module, Cutting Fluff Ovrnite/wing 295mm 9/23/2005 500648350 REPR Repaired 159277 80296971 EA 3
1023-F306400-411401-00801 50001721 Module, Cutting Fluff Ovrnite/wing 295mm 12/17/2004 500182407 REPR Repaired 118935 80296971 EA 3
1023-A614400-443401-00901-00501 1023-A615400-443401-00901-00201 50001564 50001564 3/21/2005 4/15/2005 500357639 500329094 REPL REPL Replaced Repaired 70404 70321 83 Ultrasonic stack #30 Ultrasonic stack #30 80159257 80159257 EA
1023-A615400-443401-00901-00401 1023-A614400-443401-00901-00501 50001564 50001564 4/15/2005 11/14/2005 500722482 500357639 REPL REPL Replaced Replaced 70628 70404 224 Ultrasonic stack #30 Ultrasonic stack #30 80159257 80159257 EA
—————————————————————————
can anybody has the solution for this?
Reply
Imran Mohammed
February 2, 2009 9:47 am
@qute/cute
Dude, Your query is incomplete, I spent nearly an hour working on this, But I could not understand what you want in the select statement.
You say you are trying to calculate difference, where is the difference, you have 4 columns you call them Total_Count, you are not even using any function to calculate difference, query is not clear at all and also the sample data… I mean atleast give me the select query properly, data ( insert statements )you provided are not correct comparing to the out put what you have given in your post.
Please post your complete questions.
Regards,
IM.
Reply
Imran Mohammed
February 2, 2009 10:01 am
@dyamond
When ever you use, Group BY, you need to have a aggregate function in your select list. The subquery you are trying to use has group by but no Aggregate function, also you are saying having Count(*) > 2 at one place and having Count(*) > 3 at another place, but where exactly is this count(*), are you writing this any place.
SELECT
[date],
[name],
[desc],
‘Code’ as cdDesc
FROM activity
WHERE date = ‘2009-01-05′
and site = ’80’
and exists
(
select code , count(*) Counts
from activity
where code in (‘55544′,’33333’, ‘66666’)
GROUP BY code HAVING COUNT(*) > 2
)
UNION ALL
SELECT
[date],
[name],
[desc],
‘Code2’ as cdDesc
FROM activity
WHERE date = ‘2009-01-05′
and site = ’80’
and exists
(
select code, count(*) Counts
from activity
where code in (‘2222′,’3333’, ‘66666’, ‘88888’)
GROUP BY code HAVING COUNT (*) > 3
)
Try using the code like above.
I did not test the script functionality, since I do not have sample dat, If script still does not work then please post table structure script and some sample data and expected output.
So that we can help you out.
Regards,
IM.
Reply
Sunil Kumar Basu
February 3, 2009 12:26 pm
/*
Input Param:
@vchStartDate [in yyyymmdd format]
@intDuration [specifying the no. of days for end date calculation]
This code block will calculate the end date based on the supplied start date and duration.
Finally it will return the dates and the day starting from start date to end date both inclusive.
Ist Resultset- Returns All Dates in the range starting from start date to end date.
IInd Resultset- Returns All Dates in the range starting from start date to end date excluding saturdays and sundays.
IIIrd Resultset- Returns All Dates in the range starting from start date to end date excluding weekdays.
*/
DECLARE @vchStartDate VARCHAR(10)
DECLARE @intDuration INT
DECLARE @LclvchEndDate VARCHAR(10)
/* Set input values */
SET @vchStartDate = ‘20090101’
SET @intDuration = 50
/* Calculate End Date */
SET @LclvchEndDate = CONVERT(VARCHAR(10),DATEADD(dd,@intDuration, CAST(@vchStartDate AS DATETIME)),112);
WITH mycte AS
(
SELECT CAST(@vchStartDate AS DATETIME) DateValue
UNION ALL
SELECT DateValue + 1
FROM mycte
WHERE DateValue + 1 <= @LclvchEndDate
)
SELECT DateValue
INTO #tbl_data
FROM mycte
OPTION (MAXRECURSION 0)
/* Ist Resultset- Returns All Dates in the range starting from start date to end date. */
SELECT CONVERT(VARCHAR(10),DateValue,121) Date,
[Day] =
CASE
WHEN DATEPART(dw,DateValue)= 1 THEN ‘SUNDAY’
WHEN DATEPART(dw,DateValue)= 2 THEN ‘MONDAY’
WHEN DATEPART(dw,DateValue)= 3 THEN ‘TUESDAY’
WHEN DATEPART(dw,DateValue)= 4 THEN ‘WEDNESDAY’
WHEN DATEPART(dw,DateValue)= 5 THEN ‘THURSDAY’
WHEN DATEPART(dw,DateValue)= 6 THEN ‘FRIDAY’
WHEN DATEPART(dw,DateValue)= 7 THEN ‘SATURDAY’
END
FROM #tbl_data
/* IInd Resultset- Returns All Dates in the range starting from start date to end date excluding saturdays and sundays. */
SELECT CONVERT(VARCHAR(10),DateValue,121) Date,
[Day] =
CASE
WHEN DATEPART(dw,DateValue)= 2 THEN ‘MONDAY’
WHEN DATEPART(dw,DateValue)= 3 THEN ‘TUESDAY’
WHEN DATEPART(dw,DateValue)= 4 THEN ‘WEDNESDAY’
WHEN DATEPART(dw,DateValue)= 5 THEN ‘THURSDAY’
WHEN DATEPART(dw,DateValue)= 6 THEN ‘FRIDAY’
END
FROM #tbl_data
WHERE DATEPART(dw,DateValue) NOT IN (1,7)
/* IIIrd Resultset- Returns All Dates in the range starting from start date to end date excluding weekdays. */
SELECT CONVERT(VARCHAR(10),DateValue,121) Date,
[Day] =
CASE
WHEN DATEPART(dw,DateValue)= 1 THEN ‘SUNDAY’
WHEN DATEPART(dw,DateValue)= 7 THEN ‘SATURDAY’
END
FROM #tbl_data
WHERE DATEPART(dw,DateValue) IN (1,7)
DROP TABLE #tbl_data
Reply
vishal kulshreshtha
February 5, 2009 2:10 pm
Hi pinal,
your quary is good ut it has some limitation like identity col.
i have a better idea to do delete duplicate record using CTE.
like:
CREATE TABLE [dbo].[testing3](
[id] [int] NULL,
[name] [varchar](50)
) ON [PRIMARY]
with dup as(select *,row_number() over(partition by id order by id)as TID from testing3)
delete from dup where tid >1
Reply
Divya
February 7, 2009 7:45 pm
Hello sir,
You r genious, I love your articles.
thanks
Reply
Sitaram
February 13, 2009 11:56 am
Hi Pinal,
I want to learn sql server.I am new in this field.
Thanks
Reply