Remove duplicates using SQL query

I recieve duplicate customer bill data which I want to consolidate using SQL query. How to summarize data using single query...both Qty and Price. If either one of price or qty is -ve then both should be -ve.

Here is example data for one customer.

1 – Only Qty is negative

Product Qty Price

Shirt 2 6.00

Shirt -1 6.00

-

Shirt 1 6.00 Result

2 - Only price is negative

Product Qty Price

Pant 2 6.00

Pant 1 -6.00

-

Pant 1 6.00 Result

Thanks for help!!

Pintoo

Based solely upon the limited information and sample data you provided, this solution will meet your requirements.

However, questions arise. Is the price always the same, if not, which price to return? (ABS() may be useful, so could AVG().)

Code Snippet

DECLARE @.MyTable table
( Product varchar(20),
Qty int,
Price decimal(8,2)
)

INSERT INTO @.MyTable VALUES ( 'Shirt', 2, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Shirt', -1, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Pant', 2, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Pant', 1, -6.00 )

SELECT
Product,
Qty = sum( CASE
WHEN Price < 0 THEN ( Qty * -1 )
ELSE Qty
END
),
Price = max( Price )
FROM @.MyTable
GROUP BY Product

Product Qty Price
-- -- -
Pant 1 6.00
Shirt 1 6.00

|||

Thanks Arnie.

Price is not always same. I have to calculate price by sum(price)/sum(Qty). But how can I use the quantity calculated above to calculate price.

for ex.

SELECT
Product,
Qty = sum( CASE
WHEN Price < 0 THEN ( Qty * -1 )
ELSE Qty
END
),
Price = sum(price*qty)/ sum(qty) caclulated above(how to reuse qty from above query...not sure).

FROM @.MyTable
GROUP BY Product

Thanks

Pintoo

|||

Just the way you expressed it -you were almost there.

Using the ABS() function handles the negative prices and quantities.

Code Snippet

SELECT
Product,
Qty = sum( CASE
WHEN Price < 0 THEN ( Qty * -1 )
ELSE Qty
END
),
Price = ( sum( abs( Price ) * abs( Qty )) / sum( abs( Qty )) )
FROM @.MyTable
GROUP BY Product

|||

How about this query..

DECLARE @.MyTable Table

(

Productvarchar(20),

Qtyint,

Pricedecimal(8,2)

)

INSERT INTO @.MyTable VALUES ( 'Shirt', 2, 6.00 )

INSERT INTO @.MyTable VALUES ( 'Shirt', -1, 6.00 )

INSERT INTO @.MyTable VALUES ( 'Pant', 2, 6.00 )

INSERT INTO @.MyTable VALUES ( 'Pant', 1, -6.00 )

Select

Product

, Sum(Price * Qty/abs(Price))

, Sum(Price * Qty)

From

@.MyTable

Group By

Product

|||

Mani,

With your variation, a negative Price OR Qty adversely effects the ( Price * Qty ) calculation.

Notice what happens when two additional rows are added to the dataset. (Probably not exactly what the OP had in mind...) OnHand quantities might easily be negative or zero, but the average price 'should' reflect the prices used in every transaction and therefore not be negative or zero.

Code Snippet

DECLARE @.MyTable Table
( Product varchar(20),
Qty int,
Price decimal(8,2)
)

INSERT INTO @.MyTable VALUES ( 'Shirt', 2, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Shirt', -1, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Pant', 2, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Pant', 1, -6.00 )

INSERT INTO @.MyTable VALUES ( 'Shirt', -1, 6.00 )
INSERT INTO @.MyTable VALUES ( 'Pant', 2, -6.00 )

Select
Product
, Sum( Price * Qty / abs( Price ))
, Sum( Price * Qty )
From @.MyTable
Group By Product

Product
-- - --
Pant -1.00000000000 -6.00
Shirt .00000000000 .00

Remove Duplicate value from One Cloumn Table

I am working SQL Server 2005 and One Table Which contain only one column without primary key
Now I want to remove all duplicate value from that table with only single query
Here are a couple options:
http://www.sqlteam.com/item.asp?ItemID=3331
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
|||

Thank

Here happens like that

1) Select Distinct row from the original table and store it into tempory table

2) delete all rows from original table

3) copy the all rows from tempory table (where distinct rows inserted)

But I want to do this thing using single Delete Query
Can we do this by using only single delete query

sql

Remove duplicate rows from table

I have a table with one column, and i want to remove those records from the table which are duplicate i meant if i have a records rakesh in table two time then one records should be remove...
my tables is like that

Names
----
Rakesh
Rakesh
Rakesh Kumar Sharma
Rakesh Kumar Sharma
Baburaj
Raghu
Raghu

and Output of query should be like that
Names
----
Rakesh
Rakesh Kumar Sharma
Baburaj
Raghu

Thanks in advanceSELECT * FROM table1
UNION
SELECT * FROM table1
or
SELECT distinct name FROM table1

...;)

Plz give the whole structure of your table,then only we can help you.Plz read the sticky above in this forum.|||Hi,

You can find three methods with samples for removing dublicated records from tables at article http://www.kodyaz.com/articles/delete-duplicate-records-rows-in-a-table.aspx

One method is using "SET ROWCOUNT", an other method uses the "TOP". And the last way of solving this problem is getting use of a temporary "IDENTITY" column.

I hope you find it useful

Eralper
http://www.kodyaz.com|||As always, make a back-up first!
select distinct *
into #temptable
from yourtable

delete from yourtable

insert into yourtable
select *
from #temptable

drop table #temptable

Remove duplicate rows from a table

Hi guys

I have been using SQL server 2005. I have got a huge table with about 1 million rows.

Problem is this table has got duplicate rows in lot of places. I need to remove the these duplicates. Is there an easy way to do that?

Is there a query in SQL to remove duplicate rows?

thanks

Mita

Mita:

I would start by modifying the table so that it becomes impossible to have duplicate rows. That is, you want to make it so that no two rows will ever be exactly identical. By doing this you will always have a method of selecting records according to the property that makes them unique. The easiest ways to make table rows unique are to use either (1) an identity column or (2) a unique identifier column. Look in books online for a discussion. Once you have a method of keying your records you will be able to delete records according to this record key.

Dave

|||

Hi,

refer below threads discussing same topic

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3632

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=18529

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=14484

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=13882

Hemantgiri S. Goswami

remove duplicate rows

i have a table with NO constraints and duplicate rows(e.g one row is inserted four times) now I want to remove the rows in such a way that only one row from the duplicate rows should stay in the table. Is there any query to solve this problem.
**********************************************************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...Here is your solution:
http://support.microsoft.com/default.aspx?scid=KB;en-us;q139444
"enoch jadhav" <enochjadhav@.myway.com> wrote in message
news:ejrKmIblDHA.424@.TK2MSFTNGP10.phx.gbl...
> i have a table with NO constraints and duplicate rows(e.g one row is
inserted four times) now I want to remove the rows in such a way that only
one row from the duplicate rows should stay in the table. Is there any query
to solve this problem.
> **********************************************************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

Remove Duplicate Rows

I've got the following table data:

116525.99
116520.14
129965.03
129960.12
129967.00

And I need to write a query to return only rows 2 and 4, since the
remaining rows have duplicate IDs. I've tried the Group By, but am
having no luck.

Thanks!dale...@.gmail.com wrote:
> I've got the following table data:
> 116525.99
> 116520.14
> 129965.03
> 129960.12
> 129967.00
> And I need to write a query to return only rows 2 and 4, since the
> remaining rows have duplicate IDs. I've tried the Group By, but am
> having no luck.
> Thanks!

What do you mean by "rows 2 and 4"? Those numbers refer to positions in
the list of values you posted, but SQL Server knows nothing about that
because tables in SQL have no logical order at all. In other words you
haven't given enough information to answer your question.

If these are the only two columns you have then probably the best you
can do is:

SELECT col1, MIN(col2) AS col2
FROM your_table
GROUP BY col1 ;

or:

SELECT col1, MAX(col2) AS col2
FROM your_table
GROUP BY col1 ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Why 2 and 4? Why not 1 & 3 or 1 & 5? What are you using as your
discriminator?

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<dale.zjc@.gmail.com> wrote in message
news:1144699376.547275.246250@.t31g2000cwb.googlegr oups.com...
I've got the following table data:

11652 5.99
11652 0.14
12996 5.03
12996 0.12
12996 7.00

And I need to write a query to return only rows 2 and 4, since the
remaining rows have duplicate IDs. I've tried the Group By, but am
having no luck.

Thanks!|||Thanks for the quick response!

Here's my complete query:

SELECT Object.Name, Job.JobID, MAX(Data.[Value]) as NewValue,
DATEADD(S, Data.[Time], '1jan1970') AS EventDate,
Job.KSName, GETDATE() AS CURDATE

FROM DataHeader INNER JOIN
Data ON DataHeader.DataID = Data.DataID INNER JOIN
Object INNER JOIN
Job ON Object.ObjID = Job.MachineObjID ON
DataHeader.JobID = Job.JobID

Group By Job.JobID

But I'm getting the following error:

Server: Msg 8120, Level 16, State 1, Line 1
Column 'Object.Name' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Data.Time' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Job.KSName' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.

David Portas wrote:
> dale...@.gmail.com wrote:
> > I've got the following table data:
> > 116525.99
> > 116520.14
> > 129965.03
> > 129960.12
> > 129967.00
> > And I need to write a query to return only rows 2 and 4, since the
> > remaining rows have duplicate IDs. I've tried the Group By, but am
> > having no luck.
> > Thanks!
> What do you mean by "rows 2 and 4"? Those numbers refer to positions in
> the list of values you posted, but SQL Server knows nothing about that
> because tables in SQL have no logical order at all. In other words you
> haven't given enough information to answer your question.
> If these are the only two columns you have then probably the best you
> can do is:
> SELECT col1, MIN(col2) AS col2
> FROM your_table
> GROUP BY col1 ;
> or:
> SELECT col1, MAX(col2) AS col2
> FROM your_table
> GROUP BY col1 ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/...US,SQL.90).aspx
> --|||dale...@.gmail.com wrote:
> Thanks for the quick response!
> Here's my complete query:
> SELECT Object.Name, Job.JobID, MAX(Data.[Value]) as NewValue,
> DATEADD(S, Data.[Time], '1jan1970') AS EventDate,
> Job.KSName, GETDATE() AS CURDATE
> FROM DataHeader INNER JOIN
> Data ON DataHeader.DataID = Data.DataID INNER JOIN
> Object INNER JOIN
> Job ON Object.ObjID = Job.MachineObjID ON
> DataHeader.JobID = Job.JobID
> Group By Job.JobID
> But I'm getting the following error:
> Server: Msg 8120, Level 16, State 1, Line 1
> Column 'Object.Name' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
> Server: Msg 8120, Level 16, State 1, Line 1
> Column 'Data.Time' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
> Server: Msg 8120, Level 16, State 1, Line 1
> Column 'Job.KSName' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.

Any column that you don't want to group by needs to be enclosed in an
aggregate function (MIN or MAX for example). Your problem is obviously
a bit different to what you first asked for. The best way to post a
problem like this is to include enough code so that others can
reproduce it. See:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||(dale.zjc@.gmail.com) writes:
> Thanks for the quick response!
> Here's my complete query:
> SELECT Object.Name, Job.JobID, MAX(Data.[Value]) as NewValue,
> DATEADD(S, Data.[Time], '1jan1970') AS EventDate,
> Job.KSName, GETDATE() AS CURDATE
> FROM DataHeader INNER JOIN
> Data ON DataHeader.DataID = Data.DataID INNER JOIN
> Object INNER JOIN
> Job ON Object.ObjID = Job.MachineObjID ON
> DataHeader.JobID = Job.JobID
> Group By Job.JobID

This is possible correct version of your query, but most probably not.
It's just a piece of guesswork.

SELECT o.Name, j.JobID, mx.NewValue,
DATEADD(ss, d.[Time], '1 jan 1970') AS EventDate,
j.KSName, GETDATE() AS CURDATE
FROM (SELECT j.JobID, NewValue = MAX(d.[Value])
FROM DataHeader dh
JOIN Job j ON dh.JobID = j.JobID
JOIN Data d ON dh.DataID = d.DataID) AS mx
JOIN Job j ON mx.JobID = j.JobID
JOIN DataHeader dh ON dh.JobID = j.JobID
JOIN Data d ON dh.DataID = d.DataID
JOIN Object o ON o.ObjID = j.MachineObjID
GROUP BY j.JobID

For this type of questions it helps if you include descriptions of
your tables, including keys. Preferably in form of CREATE TABLE
statements. Sample data is also a good idea, even better if as
INSERT statements, as that makes it easy to post a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

REMOVE DUPLICATE ROWS

Hi everyone.

How can I get the unique row from a table which contains multiple rows
that have exactly the same values.

example:
create table test (
c1 as smallint,
c2 as smallint,
c3 as smallint )

insert into test values (1,2,3)
insert into test values (1,2,3)

i want to remove whichever of the rows but I want to retain a single
row.

TIA

DiegoHi

There are several ways of doing this... You can select distinct rows into a
temporary table (See DISTINCT in books online), clear out your main table
and then re-populate it. If you have an differentiating column then you can
use that to delete rows that are not (say) the minimum value of that
column, or you could stop rows being put in the table in the first place by
having a unique index or primary key over the columns that should be
distinct, or using a not exists clause when inserting the data.

E.g.
..
SELECT DISTINCT *
INTO #SimpleExample
FROM Test

TRUNCATE TABLE TEST

INSERT INTO TEST ( c1, c2, c3 )
SELECT * FROM #SimpleExample

As there is no AS in a CREATE TABLE statement you will have problems with
this DDL, there is also no information regarding PKs etc which would have
been useful. See http://www.aspfaq.com/etiquette.asp?id=5006

John

"Diego Rey" <diegobph@.yahoo.com> wrote in message
news:e09be785.0412042052.52c8c7e5@.posting.google.c om...
> Hi everyone.
> How can I get the unique row from a table which contains multiple rows
> that have exactly the same values.
> example:
> create table test (
> c1 as smallint,
> c2 as smallint,
> c3 as smallint )
> insert into test values (1,2,3)
> insert into test values (1,2,3)
> i want to remove whichever of the rows but I want to retain a single
> row.
> TIA
> Diego|||John Bell (jbellnewsposts@.hotmail.com) writes:
> As there is no AS in a CREATE TABLE statement you will have problems with
> this DDL, there is also no information regarding PKs etc

Obviously, if he has identical rows in his table, there is no primary key.

Which all good tables in relational database is supposed to have, and thus
this explains why this operation is not a trivial one to perform. You are
simply not supposed to wind up in this situation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> Obviously, if he has identical rows in his table, there is no primary
>> key...Which all good tables in relational database is supposed to have,

Is that a "relational heresy" or a "relational orthodoxy" or an "attempt to
appease the ideas of relational theory" ?( ..kidding :-) )

--
Anithsql

remove duplicate row

how do i write a query that will remove duplicate rows (rows with the same
"username" field) and keep the one with lower ID
ID USERNAME
1 john
2 john2
3 john
after executing this query this should become
ID USERNAME
1 john
2 john2
Thanks,
HowardDo:
DELETE FROM tbl
WHERE id NOT IN ( SELECT MIN( t1.id )
FROM tbl t1
WHERE t1.username = tbl.username ) ;
You can also re-write this using derived table construct or using the
EXISTS() clause.
Anith|||Hi Anith,
I'll be doing the 2005 alternatives today. ;-)
WITH Dups AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY username) AS rn
FROM dbo.T1
)
DELETE FROM Dups
WHERE rn > 1;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uAMZ9Rd3FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Do:
> DELETE FROM tbl
> WHERE id NOT IN ( SELECT MIN( t1.id )
> FROM tbl t1
> WHERE t1.username = tbl.username ) ;
> You can also re-write this using derived table construct or using the
> EXISTS() clause.
> --
> Anith
>|||I should add an ORDER BY clause...
WITH Dups AS
(
SELECT *,
ROW_NUMBER()
OVER(PARTITION BY username ORDER BY id) AS rn
FROM dbo.T1
)
DELETE FROM Dups
WHERE rn > 1;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%23vqZQ%23e3FHA.1140@.tk2msftngp13.phx.gbl...
> Hi Anith,
> I'll be doing the 2005 alternatives today. ;-)
> WITH Dups AS
> (
> SELECT *,
> ROW_NUMBER() OVER(PARTITION BY username) AS rn
> FROM dbo.T1
> )
> DELETE FROM Dups
> WHERE rn > 1;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> Join us for the SQL Server 2005 launch at the SQL W in Israel!
> [url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uAMZ9Rd3FHA.3976@.TK2MSFTNGP15.phx.gbl...
>

Remove duplicate records after importing via SSIS

Hello all,
I have some phone logs that I would like to import into table on a daily or
periodic basis. I would like to be able to elliminate any duplicate records
that it imports when it appends it to the table. Is there some T-SQL that I
can run that would help with this situation? I DO have one particular
field that has a unique ID for each record, so it *should* be pretty easy, if
I only knew what I was doing. ;-)
--
SketchySince you have a unique key and existing data, set up the insert using a not
exists clause
insert realtable
select ..
from holdingtable ht
where not exists (select * from realtable rt where rt.keyfield =ht.keyfield)
TheSQLGuru
President
Indicium Resources, Inc.
"sketchy" <sketchy@.discussions.microsoft.com> wrote in message
news:AD538EA5-A55B-45C1-9992-D0AAFD0AD0C6@.microsoft.com...
> Hello all,
> I have some phone logs that I would like to import into table on a daily
> or
> periodic basis. I would like to be able to elliminate any duplicate
> records
> that it imports when it appends it to the table. Is there some T-SQL that
> I
> can run that would help with this situation? I DO have one particular
> field that has a unique ID for each record, so it *should* be pretty easy,
> if
> I only knew what I was doing. ;-)
> --
> Sketchy|||On Thu, 23 Aug 2007 07:22:30 -0700, sketchy
<sketchy@.discussions.microsoft.com> wrote:
>Hello all,
>I have some phone logs that I would like to import into table on a daily or
>periodic basis. I would like to be able to elliminate any duplicate records
>that it imports when it appends it to the table. Is there some T-SQL that I
>can run that would help with this situation? I DO have one particular
>field that has a unique ID for each record, so it *should* be pretty easy, if
>I only knew what I was doing. ;-)
Create a staging table that matches the input data layout. Import the
new data into the staging table. Insert the data from the staging
table into the production table with a WHERE NOT EXISTS test to skip
the duplicates. Truncate the staging table before loading and
processing the next set of data.
The only tricky part left is if there are duplicates in the staging
table itself, as the NOT EXISTS test only prevents inserting them when
they are already there. If the entire row is identical that can be
handled with a DISTINCT. If the row has differences in some column
other than the unique ID you need to provide more information on which
one to choose (as well as raising questions about the entire process.)
Roy Harvey
Beacon Falls, CT|||sketchy,
It would be better if you can avoid inserting the duplicated rows, as
TheSQLGuru stated. You need to work with the group of columns that make the
row unique, let us suppose that they are (c1, c2, c3), then:
delete dbo.t1
where exists (
select *
from dbo.t1 as a
where a.c1 = dbo.t1.c1
and a.c2 = dbo.t1.c2
and a.c3 = dbo.t1.c3
and a.[id] < dbo.t1.[id]
)
-- or
-- 2005
;with cte
as
(
select c1, ..., cn, row_number() over(partition by c1, c2, c3 order by [id])
as rn
from dbo.t1
)
delete cte
where rn > 1;
AMB
"sketchy" wrote:
> Hello all,
> I have some phone logs that I would like to import into table on a daily or
> periodic basis. I would like to be able to elliminate any duplicate records
> that it imports when it appends it to the table. Is there some T-SQL that I
> can run that would help with this situation? I DO have one particular
> field that has a unique ID for each record, so it *should* be pretty easy, if
> I only knew what I was doing. ;-)
> --
> Sketchy|||Wow guys. this is all great information. I never thought of having a
staging table. Let me soak this in a bit and if I have any more questions, I
know who to ask.
--
Sketchy
"Alejandro Mesa" wrote:
> sketchy,
> It would be better if you can avoid inserting the duplicated rows, as
> TheSQLGuru stated. You need to work with the group of columns that make the
> row unique, let us suppose that they are (c1, c2, c3), then:
> delete dbo.t1
> where exists (
> select *
> from dbo.t1 as a
> where a.c1 = dbo.t1.c1
> and a.c2 = dbo.t1.c2
> and a.c3 = dbo.t1.c3
> and a.[id] < dbo.t1.[id]
> )
> -- or
> -- 2005
> ;with cte
> as
> (
> select c1, ..., cn, row_number() over(partition by c1, c2, c3 order by [id])
> as rn
> from dbo.t1
> )
> delete cte
> where rn > 1;
>
> AMB
> "sketchy" wrote:
> > Hello all,
> >
> > I have some phone logs that I would like to import into table on a daily or
> > periodic basis. I would like to be able to elliminate any duplicate records
> > that it imports when it appends it to the table. Is there some T-SQL that I
> > can run that would help with this situation? I DO have one particular
> > field that has a unique ID for each record, so it *should* be pretty easy, if
> > I only knew what I was doing. ;-)
> > --
> > Sketchy|||Okay, so here is what I have done. (this is a SQL 2005 DB by the way...)
1. I have my original table, which will be used for my reporting needs,
called 'phones'. It has a handfull of fields, (e.g. "Field1" "Field2"
"Field3" etc. but the main field that has the unique info in it is called
"GlobalCallID"
2. I have created a new table, which will be used for my staging of data,
called 'phonelogstaging'. This database has the EXACT same field names and
types.
3. I've set up SSIS to import my log files into the staging table
("phonelogstaging"). It wipes out any previous data in this staging table,
so there is no possibility of duplicates in this table. Everything is
working good there.
Both tables have a field called GlobalCallID that has the unique number in
it that I should be able to check against.
So considering the above, how would my statement look?
--
Sketchy
"Roy Harvey" wrote:
> On Thu, 23 Aug 2007 07:22:30 -0700, sketchy
> <sketchy@.discussions.microsoft.com> wrote:
> >Hello all,
> >
> >I have some phone logs that I would like to import into table on a daily or
> >periodic basis. I would like to be able to elliminate any duplicate records
> >that it imports when it appends it to the table. Is there some T-SQL that I
> >can run that would help with this situation? I DO have one particular
> >field that has a unique ID for each record, so it *should* be pretty easy, if
> >I only knew what I was doing. ;-)
> Create a staging table that matches the input data layout. Import the
> new data into the staging table. Insert the data from the staging
> table into the production table with a WHERE NOT EXISTS test to skip
> the duplicates. Truncate the staging table before loading and
> processing the next set of data.
> The only tricky part left is if there are duplicates in the staging
> table itself, as the NOT EXISTS test only prevents inserting them when
> they are already there. If the entire row is identical that can be
> handled with a DISTINCT. If the row has differences in some column
> other than the unique ID you need to provide more information on which
> one to choose (as well as raising questions about the entire process.)
> Roy Harvey
> Beacon Falls, CT
>|||Okay, so here is what I have done. (this is a SQL 2005 DB by the way...)
1. I have my original table, which will be used for my reporting needs,
called 'phones'. It has a handfull of fields, (e.g. "Field1" "Field2"
"Field3" etc. but the main field that has the unique info in it is called
"GlobalCallID"
2. I have created a new table, which will be used for my staging of data,
called 'phonelogstaging'. This database has the EXACT same field names and
types.
3. I've set up SSIS to import my log files into the staging table
("phonelogstaging"). It wipes out any previous data in this staging table,
so there is no possibility of duplicates in this table. Everything is
working good there.
Both tables have a field called GlobalCallID that has the unique number in
it that I should be able to check against.
So considering the above, how would my statement look?
--
Sketchy
"TheSQLGuru" wrote:
> Since you have a unique key and existing data, set up the insert using a not
> exists clause
> insert realtable
> select ..
> from holdingtable ht
> where not exists (select * from realtable rt where rt.keyfield => ht.keyfield)
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "sketchy" <sketchy@.discussions.microsoft.com> wrote in message
> news:AD538EA5-A55B-45C1-9992-D0AAFD0AD0C6@.microsoft.com...
> > Hello all,
> >
> > I have some phone logs that I would like to import into table on a daily
> > or
> > periodic basis. I would like to be able to elliminate any duplicate
> > records
> > that it imports when it appends it to the table. Is there some T-SQL that
> > I
> > can run that would help with this situation? I DO have one particular
> > field that has a unique ID for each record, so it *should* be pretty easy,
> > if
> > I only knew what I was doing. ;-)
> > --
> > Sketchy
>
>|||Okay, so here is what I have done. (this is a SQL 2005 DB by the way...)
1. I have my original table, which will be used for my reporting needs,
called 'phones'. It has a handfull of fields, (e.g. "Field1" "Field2"
"Field3" etc. but the main field that has the unique info in it is called
"GlobalCallID"
2. I have created a new table, which will be used for my staging of data,
called 'phonelogstaging'. This database has the EXACT same field names and
types.
3. I've set up SSIS to import my log files into the staging table
("phonelogstaging"). It wipes out any previous data in this staging table,
so there is no possibility of duplicates in this table. Everything is
working good there.
Both tables have a field called GlobalCallID that has the unique number in
it that I should be able to check against.
So considering the above, how would my statement look?
--
Sketchy
"Alejandro Mesa" wrote:
> sketchy,
> It would be better if you can avoid inserting the duplicated rows, as
> TheSQLGuru stated. You need to work with the group of columns that make the
> row unique, let us suppose that they are (c1, c2, c3), then:
> delete dbo.t1
> where exists (
> select *
> from dbo.t1 as a
> where a.c1 = dbo.t1.c1
> and a.c2 = dbo.t1.c2
> and a.c3 = dbo.t1.c3
> and a.[id] < dbo.t1.[id]
> )
> -- or
> -- 2005
> ;with cte
> as
> (
> select c1, ..., cn, row_number() over(partition by c1, c2, c3 order by [id])
> as rn
> from dbo.t1
> )
> delete cte
> where rn > 1;
>
> AMB
> "sketchy" wrote:
> > Hello all,
> >
> > I have some phone logs that I would like to import into table on a daily or
> > periodic basis. I would like to be able to elliminate any duplicate records
> > that it imports when it appends it to the table. Is there some T-SQL that I
> > can run that would help with this situation? I DO have one particular
> > field that has a unique ID for each record, so it *should* be pretty easy, if
> > I only knew what I was doing. ;-)
> > --
> > Sketchy|||On Thu, 23 Aug 2007 10:20:00 -0700, sketchy
<sketchy@.discussions.microsoft.com> wrote:
>Okay, so here is what I have done. (this is a SQL 2005 DB by the way...)
>1. I have my original table, which will be used for my reporting needs,
>called 'phones'. It has a handfull of fields, (e.g. "Field1" "Field2"
>"Field3" etc. but the main field that has the unique info in it is called
>"GlobalCallID"
>2. I have created a new table, which will be used for my staging of data,
>called 'phonelogstaging'. This database has the EXACT same field names and
>types.
>3. I've set up SSIS to import my log files into the staging table
>("phonelogstaging"). It wipes out any previous data in this staging table,
>so there is no possibility of duplicates in this table. Everything is
>working good there.
>Both tables have a field called GlobalCallID that has the unique number in
>it that I should be able to check against.
>So considering the above, how would my statement look?
Assuming that data already in the table does not need to be refreshed,
only new data added:
INSERT phones
SELECT <column list>
FROM phonelogstaging as A
WHERE NOT EXISTS
(SELECT * FROM phones as B
WHERE A.GlobalCallID = B.GlobalCallID)
If the incoming data itself has duplicates, add DISTINCT after the
word SELECT.
If you need to refresh the rest of the columns of matching rows from
the staging data, you would also run the following BEFORE the command
above.
UPDATE phones
SET col1 = A.col1,
col2 = A.col2
FROM phonelogstaging as A
WHERE phones.GlobalCallID = A.GlobalCallID
Roy Harvey
Beacon Falls, CT|||Hi Roy,
Thank you SO MUCH for your quick response.
1. Yes, only new data needs to be added. No data needs to be refreshed, so
that's good.
2. One little wrinkle in the plan is that much to my dismay, it does appear
that the "GlobalCallID" field isn't necessarily a unique number, but I do
have a field adjacent to it ("CallNumber") where no records would ever have
the same combination of the two. How would you ammend your last statement to
accomodate for this? (Ugh, I know... that probably doesn't make things
simpler)
--
Sketchy
"Roy Harvey" wrote:
> On Thu, 23 Aug 2007 10:20:00 -0700, sketchy
> <sketchy@.discussions.microsoft.com> wrote:
> >Okay, so here is what I have done. (this is a SQL 2005 DB by the way...)
> >
> >1. I have my original table, which will be used for my reporting needs,
> >called 'phones'. It has a handfull of fields, (e.g. "Field1" "Field2"
> >"Field3" etc. but the main field that has the unique info in it is called
> >"GlobalCallID"
> >
> >2. I have created a new table, which will be used for my staging of data,
> >called 'phonelogstaging'. This database has the EXACT same field names and
> >types.
> >
> >3. I've set up SSIS to import my log files into the staging table
> >("phonelogstaging"). It wipes out any previous data in this staging table,
> >so there is no possibility of duplicates in this table. Everything is
> >working good there.
> >
> >Both tables have a field called GlobalCallID that has the unique number in
> >it that I should be able to check against.
> >
> >So considering the above, how would my statement look?
> Assuming that data already in the table does not need to be refreshed,
> only new data added:
> INSERT phones
> SELECT <column list>
> FROM phonelogstaging as A
> WHERE NOT EXISTS
> (SELECT * FROM phones as B
> WHERE A.GlobalCallID = B.GlobalCallID)
> If the incoming data itself has duplicates, add DISTINCT after the
> word SELECT.
> If you need to refresh the rest of the columns of matching rows from
> the staging data, you would also run the following BEFORE the command
> above.
> UPDATE phones
> SET col1 = A.col1,
> col2 = A.col2
> FROM phonelogstaging as A
> WHERE phones.GlobalCallID = A.GlobalCallID
> Roy Harvey
> Beacon Falls, CT
>|||Just add the new field that DOES make a row unique to the not exists clause:
> INSERT phones
> SELECT <column list>
> FROM phonelogstaging as A
> WHERE NOT EXISTS
> (SELECT * FROM phones as B
> WHERE A.GlobalCallID = B.GlobalCallID
and a.CallNumber = b.CallNumber)
You can use as many fields as you need to ensure uniqueness.
--
TheSQLGuru
President
Indicium Resources, Inc.
"sketchy" <sketchy@.discussions.microsoft.com> wrote in message
news:CCA5542F-5C15-413A-8B8F-D2200C1BEAD3@.microsoft.com...
> Hi Roy,
> Thank you SO MUCH for your quick response.
> 1. Yes, only new data needs to be added. No data needs to be refreshed,
> so
> that's good.
> 2. One little wrinkle in the plan is that much to my dismay, it does
> appear
> that the "GlobalCallID" field isn't necessarily a unique number, but I do
> have a field adjacent to it ("CallNumber") where no records would ever
> have
> the same combination of the two. How would you ammend your last statement
> to
> accomodate for this? (Ugh, I know... that probably doesn't make things
> simpler)
> --
> Sketchy
>
> "Roy Harvey" wrote:
>> On Thu, 23 Aug 2007 10:20:00 -0700, sketchy
>> <sketchy@.discussions.microsoft.com> wrote:
>> >Okay, so here is what I have done. (this is a SQL 2005 DB by the
>> >way...)
>> >
>> >1. I have my original table, which will be used for my reporting needs,
>> >called 'phones'. It has a handfull of fields, (e.g. "Field1" "Field2"
>> >"Field3" etc. but the main field that has the unique info in it is
>> >called
>> >"GlobalCallID"
>> >
>> >2. I have created a new table, which will be used for my staging of
>> >data,
>> >called 'phonelogstaging'. This database has the EXACT same field names
>> >and
>> >types.
>> >
>> >3. I've set up SSIS to import my log files into the staging table
>> >("phonelogstaging"). It wipes out any previous data in this staging
>> >table,
>> >so there is no possibility of duplicates in this table. Everything is
>> >working good there.
>> >
>> >Both tables have a field called GlobalCallID that has the unique number
>> >in
>> >it that I should be able to check against.
>> >
>> >So considering the above, how would my statement look?
>> Assuming that data already in the table does not need to be refreshed,
>> only new data added:
>> INSERT phones
>> SELECT <column list>
>> FROM phonelogstaging as A
>> WHERE NOT EXISTS
>> (SELECT * FROM phones as B
>> WHERE A.GlobalCallID = B.GlobalCallID)
>> If the incoming data itself has duplicates, add DISTINCT after the
>> word SELECT.
>> If you need to refresh the rest of the columns of matching rows from
>> the staging data, you would also run the following BEFORE the command
>> above.
>> UPDATE phones
>> SET col1 = A.col1,
>> col2 = A.col2
>> FROM phonelogstaging as A
>> WHERE phones.GlobalCallID = A.GlobalCallID
>> Roy Harvey
>> Beacon Falls, CT|||It works!!!! ...Thanks guys for all of your help in this. I hope for some
good computer karma to come your way.
One interesting little tidbit is that I was unable to get it to work by
specifying all of the fields in the Select statement. No matter what I did,
it always came back with the error of:
Insert Error: Column name or number of supplied values does not match table
definition.
It was odd because that table was the exact same as the other one. So I
just changed it to "*" and all worked well.
--
Sketchy
"TheSQLGuru" wrote:
> Just add the new field that DOES make a row unique to the not exists clause:
>
> > INSERT phones
> > SELECT <column list>
> > FROM phonelogstaging as A
> > WHERE NOT EXISTS
> > (SELECT * FROM phones as B
> > WHERE A.GlobalCallID = B.GlobalCallID
> and a.CallNumber = b.CallNumber)
> You can use as many fields as you need to ensure uniqueness.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "sketchy" <sketchy@.discussions.microsoft.com> wrote in message
> news:CCA5542F-5C15-413A-8B8F-D2200C1BEAD3@.microsoft.com...
> > Hi Roy,
> >
> > Thank you SO MUCH for your quick response.
> >
> > 1. Yes, only new data needs to be added. No data needs to be refreshed,
> > so
> > that's good.
> >
> > 2. One little wrinkle in the plan is that much to my dismay, it does
> > appear
> > that the "GlobalCallID" field isn't necessarily a unique number, but I do
> > have a field adjacent to it ("CallNumber") where no records would ever
> > have
> > the same combination of the two. How would you ammend your last statement
> > to
> > accomodate for this? (Ugh, I know... that probably doesn't make things
> > simpler)
> > --
> > Sketchy
> >
> >
> > "Roy Harvey" wrote:
> >
> >> On Thu, 23 Aug 2007 10:20:00 -0700, sketchy
> >> <sketchy@.discussions.microsoft.com> wrote:
> >>
> >> >Okay, so here is what I have done. (this is a SQL 2005 DB by the
> >> >way...)
> >> >
> >> >1. I have my original table, which will be used for my reporting needs,
> >> >called 'phones'. It has a handfull of fields, (e.g. "Field1" "Field2"
> >> >"Field3" etc. but the main field that has the unique info in it is
> >> >called
> >> >"GlobalCallID"
> >> >
> >> >2. I have created a new table, which will be used for my staging of
> >> >data,
> >> >called 'phonelogstaging'. This database has the EXACT same field names
> >> >and
> >> >types.
> >> >
> >> >3. I've set up SSIS to import my log files into the staging table
> >> >("phonelogstaging"). It wipes out any previous data in this staging
> >> >table,
> >> >so there is no possibility of duplicates in this table. Everything is
> >> >working good there.
> >> >
> >> >Both tables have a field called GlobalCallID that has the unique number
> >> >in
> >> >it that I should be able to check against.
> >> >
> >> >So considering the above, how would my statement look?
> >>
> >> Assuming that data already in the table does not need to be refreshed,
> >> only new data added:
> >>
> >> INSERT phones
> >> SELECT <column list>
> >> FROM phonelogstaging as A
> >> WHERE NOT EXISTS
> >> (SELECT * FROM phones as B
> >> WHERE A.GlobalCallID = B.GlobalCallID)
> >>
> >> If the incoming data itself has duplicates, add DISTINCT after the
> >> word SELECT.
> >>
> >> If you need to refresh the rest of the columns of matching rows from
> >> the staging data, you would also run the following BEFORE the command
> >> above.
> >>
> >> UPDATE phones
> >> SET col1 = A.col1,
> >> col2 = A.col2
> >> FROM phonelogstaging as A
> >> WHERE phones.GlobalCallID = A.GlobalCallID
> >>
> >> Roy Harvey
> >> Beacon Falls, CT
> >>
>
>

Remove duplicate entries in a search

Is there a way to find only unique entries for a search. Currently I am
looking for jobs that a run for a give database, but the script will
return multiple job entries for the same job if the database is
reference many times in that job, (once per reference) is there a way
to weed out the duplicates?
Thanks.
-Matt-
<code>
select sj.name
from msdb.dbo.sysjobs sj join msdb.dbo.sysjobsteps sjs
on sj.job_id = sjs.job_id
where sjs.database_name = 'database_name' order by 'name'
</code>Try,
select sj.name
from msdb.dbo.sysjobs sj
where exists (
select *
from msdb.dbo.sysjobsteps sjs
where sjs.database_name = 'database_name' and sjs.job_id = sj.job_id
)
order by 'name'
go
You can also use "DISTINCT" in the original statement.
AMB
"Matthew" wrote:

> Is there a way to find only unique entries for a search. Currently I am
> looking for jobs that a run for a give database, but the script will
> return multiple job entries for the same job if the database is
> reference many times in that job, (once per reference) is there a way
> to weed out the duplicates?
> Thanks.
> -Matt-
>
> <code>
> select sj.name
> from msdb.dbo.sysjobs sj join msdb.dbo.sysjobsteps sjs
> on sj.job_id = sjs.job_id
> where sjs.database_name = 'database_name' order by 'name'
> </code>
>|||Thanks
Works perfectly.

Remove Duplicate Data but Keep One

I have the following:

ID FNAME LNAME
1 John Doe
1 John Doe
1 John Doe
2 Joe Doe
2 Joe Doe
2 Joe Doe
3 John Jones
4 Foo Foo

I would like to end up like the following:

ID FNAME LNAME
1 John Doe
2 Joe Doe
3 John Jones
4 Foo Foo

Thanks for any advice
JESELECT DISTINCT * INTO someothertable FROM yourTable

DROP yourTable

RENAME someothertable TO yourTable

Remove Duplicate Data

I have a query that for one reason or another produces duplicate information in the result set. I have tried using DISTINCT and GROUP BY to remove the duplicates but because of the nature of the data I cannot get this to work, here is an example fo the data I am working with

ID Name Add1 Add2
1 Matt 16 Nowhere St Glasgow
1 Matt 16 Nowhere St Glasgow, Scotland
2 Jim 23 Blue St G65 TX
3 Bill 45 Red St
3 Bill 45 red St London

The problem is that a user can have one or more addresses!! I would like to be able to remove the duplicates by keeping the first duplicate ID that appears and getting rid of the second one. Any ideas?

Cheers1. think about normalizing your data ...
2. add some primary key (identity column primaryid?)
3. then try following

select * from yourtable y
where y.primaryid = (select min(yourtable.primaryid) from yourtable y2 where y2.id = y.id)

or

select * from yourtable y1
join (select min(primaryid) as primaryid from yourtable group by id) y2 on y2.primaryid = y1.primaryid

or something like that...|||Hi Ludenka,

Thanks for that. Unfortunately Normalising the data is not an option, I just have to work with it the way it is...Managed to get it to work using a rather long winded stored procedure but I'll have a look at your suggestion and see if I can do it a better way:

DECLARE @.patno int
DECLARE @.patfname varChar(255)
DECLARE @.patsname varChar(255)
DECLARE @.DOB DATETIME
DECLARE @.patadd1 varChar(255)
DECLARE @.patadd2 varChar(255)
DECLARE @.PreviousPatientID int

CREATE TABLE #tmp_tblPrivDemo (
patno int,
patfname varChar(255),
patsname varChar(255),
DOB DATETIME,
patadd1 varChar(255),
patadd2 varChar(255)
)

--First of all load all Patient details into a cursor
DECLARE PatientIDCursor CURSOR FOR
SELECT AxTbl_Patient.Patient_ID,
AxTbl_Patient.Forename1,
AxTbl_Patient.Surname,
AxTbl_Patient.Date_Of_Birth,
AxTbl_Patient_Address.Address1,
AxTbl_Patient_Address.Address2
FROM
AxTbl_Patient
LEFT JOIN AxTbl_Patient_Address ON AxTbl_Patient_Address.Patient_ID = AxTbl_Patient.Patient_ID
LEFT JOIN AxTbl_Patient_GP ON AxTbl_Patient_GP.Patient_ID = AxTbl_Patient.Patient_ID
LEFT JOIN [SpFrm_Follow_Up_Death_Details_v1-0] ON [SpFrm_Follow_Up_Death_Details_v1-0].Patient_ID = AxTbl_Patient.Patient_ID
INNER JOIN [SpFrm_Diagnostic_Procedures_v1-1] ON [SpFrm_Diagnostic_Procedures_v1-1].Patient_ID = AxTbl_Patient.Patient_ID
WHERE
(([SpFrm_Diagnostic_Procedures_v1-1].[Date of Diagnosis] >= '01-Apr-2003' AND [SpFrm_Diagnostic_Procedures_v1-1].[Date of Diagnosis] <= '30-Sep-2003') AND (AxTbl_Patient_Address.IsCurrentAddress = 'yes' OR AxTbl_Patient_Address.IsCurrentAddress IS NULL) AND (AxTbl_Patient_GP.Date_GP_Changed IS NULL))

OPEN PatientIDCursor
FETCH NEXT FROM PatientIDCursor INTO @.patno, @.patfname, @.patsname, @.DOB, @.patadd1, @.patadd2
SET @.PreviousPatientID = 0

WHILE @.@.FETCH_STATUS = 0
BEGIN
--Then only add to temp table if not already added, so no dups.
IF @.PreviousPatientID <> @.patno
BEGIN
INSERT INTO #tmp_tblPrivDemo
SELECT @.patno, @.patfname, @.patsname, @.DOB, @.patadd1, @.patadd2
END
SET @.PreviousPatientID = @.patno
FETCH NEXT FROM PatientIDCursor INTO @.patno, @.patfname, @.patsname, @.DOB, @.patadd1, @.patadd2

END --end loop.
CLOSE PatientIDCursor
DEALLOCATE PatientIDCursor

SELECT * FROM #tmp_tblPrivDemo ORDER BY patno
DROP TABLE #tmp_tblPrivDemo|||well, normalizing is not important for the task, it was only a hint...

are you able to add the identity column?
if yes, then my solution should work
if not, you could replace all of those

y1.primaryid = y2.primaryid

with comparison off all in table included columns, but ... it's not nice at all|||I have a query that for one reason or another produces duplicate information in the result set. I have tried using DISTINCT and GROUP BY to remove the duplicates but because of the nature of the data I cannot get this to work, here is an example fo the data I am working with

ID Name Add1 Add2
1 Matt 16 Nowhere St Glasgow
1 Matt 16 Nowhere St Glasgow, Scotland
2 Jim 23 Blue St G65 TX
3 Bill 45 Red St
3 Bill 45 red St London

The problem is that a user can have one or more addresses!! I would like to be able to remove the duplicates by keeping the first duplicate ID that appears and getting rid of the second one. Any ideas?

Cheers

perhaps a quicker way of doing it than your cursor would be:

declare @.temp_name_addr TABLE (
id int not null,
name char(n) not null,
add1 char(n) null,
add2 char(n) null
)

insert @.temp_name_addr (id, name)
select distinct id, name
from permanent_table

update T
set T.add1 = P.add1,
T.add2 = P.add2
from @.temp_name_addr T,
permanent_table P
where T.id = P.id

select * from @.temp_name_addr

...of course which address you get will be arbitrary (both in your solution and mine) there's no rigid "first" without an order by clause...sql

Remove Duplicate

HI All,
I want to remove duplicate records from my table based on nic number. I try to put primray key constraint. But there are many many duplicates so cannot do it can I have a query to remove duplicates..
Thnx
;)
ShaniOriginally posted by shani
HI All,

I want to remove duplicate records from my table based on nic number. I try to put primray key constraint. But there are many many duplicates so cannot do it can I have a query to remove duplicates..

Thnx
;)
Shani

http://www.cleardata.biz/articles/dupes.aspx
http://www.sqlteam.com/item.asp?ItemID=3331|||Originally posted by shani
HI All,

I want to remove duplicate records from my table based on nic number. I try to put primray key constraint. But there are many many duplicates so cannot do it can I have a query to remove duplicates..

Thnx
;)
Shani

read this:
http://www.databasejournal.com/features/mssql/article.php/2235081