Remove Duplicate Rows - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Remove Duplicate Rows

Author

Topic

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-06-14 : 06:00:25

Dear All,
I am using this query to retrive values from more than one table.But this query returns more than one value.Now I want to get the only the distinct filenames only.
How can I do this

select d.bookname,e.stagecode,c.activitycode,a.filename,
f.loginname,b.lockedby,a.filesize,a.createdtime,g.outext 
from filedetails as a  inner Join recorddetails_1
as b on a.fileid=b.fileid inner Join activitydetails as
c on c.activityid=b.activityid inner join bookdetails as
d on a.bookid=d.bookid  inner join stagedetails as e on 
a.stageid=e.stageid  inner join empdetails as f on a.empid
= f.isno inner join tOutputExtensionDetails g 
on a.typeid=g.id and b.lockedby in ('534','536')

Dana

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-06-14 : 09:30:58

You need to use a distinct statement or group by clause. Please keep in mind that if you add more columns to the output, that this will effect the group by.

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-06-14 : 09:53:22

I tried like this,
select d.bookname,e.stagecode,c.activitycode,distinct(a.filename)

But I gave Error ?

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-06-14 : 10:24:39

If you need to return multiple columns, then you'll need a group by clause ...

select d.bookname,e.stagecode,c.activitycode,a.filename
from yourtables
group by select d.bookname,e.stagecode,c.activitycode,a.filename

Now please keep in mind if any of those fields have a different value, then you will get dups. For example : a book that has two different filenames will result in a row returned for each filename.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources