how can i remove thsi duplicate

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how can i remove thsi duplicate

Author

Topic

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-18 : 06:16:30

I am running a query where i am joining few tables to the fact table and i get the duplicate in the result as shown below

key Org FD Desc Age Let LetDes 1
8xx 85 17/09/2012 1.. NULL 20 6.. Al
8xx 85 17/09/2012 1.. NULL 20 6.. Al
8xx 85 11/12/2011 1.. NULL 45 6.. Al
8xx 85 09/01/1999 1.. NULL 32 6.. Al
8xx 85 17/07/1995 1.. NULL 12 6.. Al

as you can see the first two rows in the table are duplicates how can i remove these?

thank you

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-01-18 : 06:41:57

GROUP BY or ROW_NUMBER OVER()

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-18 : 06:42:02

I have presented the result more clear below the first two rows are duplicate how can i take these duplicates out? or even better do the query so it excludes such duplicates

key Org FD Desc Age Let LetDes 1
8xx 85 17/09/2012 1.. NULL 20 6.. Al
8xx 85 17/09/2012 1.. NULL 20 6.. Al
8xx 85 11/12/2011 1.. NULL 45 6.. Al
8xx 85 09/01/1999 1.. NULL 32 6.. Al
8xx 85 17/07/1995 1.. NULL 12 6.. Al

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-18 : 06:42:35

DonAtWork how do you mean?

thanks

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-01-18 : 06:43:51

Now show us the query you used to get your data, we can show you how to get rid of the duplicates.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-18 : 06:45:05

DonAtWork the ROW_NUMBER OVER() will just put a squence number infornt of it it will still be a duplicate when the manager has a look at it

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-01-18 : 06:53:56

create table #foo (yak int)

insert into #foo
(yak )
values
(1),(2),(3),(4),(4),(5)

select * from #foo
select yak from
(select yak, ROW_NUMBER() OVER(PARTITION BY yak ORDER BY yak ) as rn
from #foo )x
where rn = 1

drop table #foo

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-18 : 10:00:08

DonAtWork

below is the query i am running

SELECT dbo.F.[Key], dbo.N.Org, dbo.D.FD,dbo.R.[Desc], dbo.F.Age,
dbo.Di.Let, dbo.Dia.LetDes, dbo.F.1
FROM dbo.Fact INNER JOIN
dbo.N ON dbo.F.Orgkey = dbo.N.OrgKey INNER JOIN
dbo.R ON dbo.F.Key = dbo.R.RKey INNER JOIN
dbo.D ON dbo.F.DKey = dbo.DD.DKey

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-18 : 11:08:44

anyone????

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-18 : 11:37:54

SELECT DISTINCT

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-18 : 11:40:32

select distinct row?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-01-18 : 14:35:22

Sorry, had to do actual work.

Just add DISTINCT to your SELECT statement like brett suggested.

SELECT DISTINCT dbo.F.[Key], dbo.N.Org, dbo.D.FD,dbo.R.[Desc], dbo.F.Age,
dbo.Di.Let, dbo.Dia.LetDes, dbo.F.1
FROM dbo.Fact INNER JOIN
dbo.N ON dbo.F.Orgkey = dbo.N.OrgKey INNER JOIN
dbo.R ON dbo.F.Key = dbo.R.RKey INNER JOIN
dbo.D ON dbo.F.DKey = dbo.DD.DKey

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-19 : 07:28:35

thanks mate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources