Duplicate record removal

I can identify the duplicate rows and show number of duplication by using
this
SELECT [USER_NUMBER], [EMAIL], Count([USER_NUMBER]) AS NUM
FROM USERS
GROUP BY [USER_NUMBER], [EMAIL]
HAVING Count([USER_NUMBER]) > 1
I can't figure out how to delete duplicate records and keep one distinct
record with smallest ID
Example:
TABLE: USERS
ID USER_NUMBER EMAIL
1 12 a@.a.com
2 12 a@.a.com
3 13 b@.b.com
4 14 c@.c.com
5 14 c@.c.com
Output:
ID USER_NUMBER EMAIL
1 12 a@.a.com
3 13 b@.b.com
4 14 c@.c.com
Thanks,
HowardDELETE FROM users
WHERE EXISTS
(SELECT *
FROM users AS U
WHERE U.user_number = users.user_number
AND U.email = users.email
AND U.id < users.id) ;
Nulls, if any, will be ignored.
David Portas
SQL Server MVP
--|||Hi Howard,
Try this statement
Delete From USERS
Where [USER_NUMBER] NOT In
(Select [USER_NUMBER] From USERS Users_Out Where [USER_NUMBER]
IN (Select Min(Users_In.[USER_NUMBER]) From USERS Users_In Where
Users_In.[EMAIL] = Users_Out.[EMAIL]
)
)
PLEASE NOTE : I have not tested this statement so please run the following
query and ensure that this is what you want to be deleted.
Select * From USERS
Where [USER_NUMBER] NOT In
(Select [USER_NUMBER] From USERS Users_Out Where [USER_NUMBER]
IN (Select Min(Users_In.[USER_NUMBER]) From USERS Users_In Where
Users_In.[EMAIL] = Users_Out.[EMAIL]
)
)
Do mail if this post help s you.
Vishal Khajuria
9886170165
IBM Bangalore
"Howard" wrote:

> I can identify the duplicate rows and show number of duplication by using
> this
> SELECT [USER_NUMBER], [EMAIL], Count([USER_NUMBER]) AS NUM
> FROM USERS
> GROUP BY [USER_NUMBER], [EMAIL]
> HAVING Count([USER_NUMBER]) > 1
>
> I can't figure out how to delete duplicate records and keep one distinct
> record with smallest ID
> Example:
> TABLE: USERS
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 2 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> 5 14 c@.c.com
>
> Output:
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> Thanks,
> Howard
>
>
>|||thanks for your replies
I solved the problem.
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OlFbAlQ6FHA.3588@.TK2MSFTNGP15.phx.gbl...
>I can identify the duplicate rows and show number of duplication by using
>this
> SELECT [USER_NUMBER], [EMAIL], Count([USER_NUMBER]) AS NUM
> FROM USERS
> GROUP BY [USER_NUMBER], [EMAIL]
> HAVING Count([USER_NUMBER]) > 1
>
> I can't figure out how to delete duplicate records and keep one distinct
> record with smallest ID
> Example:
> TABLE: USERS
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 2 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> 5 14 c@.c.com
>
> Output:
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> Thanks,
> Howard
>
>|||Hi,
This will solve your query ...
delete from test where test.id not in (select top 1 b.id from test b
where (test.user_number=b.user_number) and
(test.email=b.email))
Regards,
Predrag Stojanovic
Analist/Programer
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OlFbAlQ6FHA.3588@.TK2MSFTNGP15.phx.gbl...
> I can identify the duplicate rows and show number of duplication by using
> this
> SELECT [USER_NUMBER], [EMAIL], Count([USER_NUMBER]) AS NUM
> FROM USERS
> GROUP BY [USER_NUMBER], [EMAIL]
> HAVING Count([USER_NUMBER]) > 1
>
> I can't figure out how to delete duplicate records and keep one distinct
> record with smallest ID
> Example:
> TABLE: USERS
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 2 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> 5 14 c@.c.com
>
> Output:
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> Thanks,
> Howard
>
>|||You missed out the ORDER BY. TOP 1 may not retrieve the minimum value
of ID unless you use ORDER BY.
Also, notice that this version will not delete any rows for a
particular user_name if a NULL ID exists for that user_name. Probably
that's not likely - I'd guess that ID is the PRIMARY KEY - but that's
one potential catch with TOP and with NOT IN.
David Portas
SQL Server MVP
--|||Here is the process:
1. SELECT * INTO #Temp1 FROM [Table1]
2. TRUNCATE TABLE [Table1]
3. CREATE UNIQUE INDEX [Index1] ON [Table1] (Unique Column Names) WITH
IGNORE_DUP_KEY
4. INSERT INTO [Table1] (Column Names) SELECT (Column Names) FROM [Table1]
5. DROP INDEX [Table1].[Index1]
Substitute your own names for those within the [].
Unique Column Names are those columns that uniquely identify each row.
Column Names is the full list of columns in your table.
HTH,
Mike
"Howard" wrote:

> I can identify the duplicate rows and show number of duplication by using
> this
> SELECT [USER_NUMBER], [EMAIL], Count([USER_NUMBER]) AS NUM
> FROM USERS
> GROUP BY [USER_NUMBER], [EMAIL]
> HAVING Count([USER_NUMBER]) > 1
>
> I can't figure out how to delete duplicate records and keep one distinct
> record with smallest ID
> Example:
> TABLE: USERS
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 2 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> 5 14 c@.c.com
>
> Output:
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> Thanks,
> Howard
>
>
>sql

Dumb question: Sanpshot reports

Will a report based on a scheduled snapshot send itself out (either to an email or data space) if the data in the new snap shot is the same as the previous?

If so is there any method to only send out a report if the data has changed?

And if not what do most people use snap shot based subscriptions for?

Thanks.

1.) Yes.

2.) You can try to use Data Driven Subscriptions. What you will have to do is have the query which generates the list of recipients detect if the data has changed, and return an empty set if this is the case. SSRS will not really help you do this, so you will need to cook up your own way of determining if the data has changed since the last report went out.

DTS to export formated date to excel

Hello,

I am trying to output data from my sql table to an excel spreadsheet and send it by email which works fine, the problem is he wants the date to be in the format d-mmm-yy, which is easy to format in excel manually, but he do not want to do this manually. I tried to do this when I select the date from the table to spreadsheet, "select convert(char,value_date,106) from table", but this don't get transported to the excel spreadsheet, I get my results on the spread sheet as dd/mm/yy. Can you please help either to set the date on excel forever to be in this format "d-mmm-yy" or to force this output to excelOk, i manage to answer myself, you need to format the cells onto the excel file itself