Query help, distinct row - 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)

Query help, distinct row

Author

Topic

tehprince
Starting Member

9 Posts

Posted - 2008-04-21 : 12:08:52

I have the following query:

SELECT DISTINCT PC.ContentID, PC.GeoUnitID, PC.ContactID, CT.ID, CT.PageTitle, C.FirstName, C.LastName, C.MainPhoneNO, C.EmailAddress
FROM ProductContacts PC INNER JOIN Content CT ON PC.ContentID = CT.ID INNER JOIN Contacts C ON PC.ContactID = C.ContactID
WHERE PC.GeoUnitID = @GeoUnitID
ORDER BY CT.PageTitle ASC

The problem I'm having is two rows have the same ContentID with a different ContactID. I want to only return a single row for each ContentID.

Is there a way around this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-21 : 12:20:27

What are your business rules for that? You need the record with max or min value of ContactID?

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-21 : 12:20:41

Which ContactID do you want?

SELECT DISTINCT PC.ContentID, PC.GeoUnitID, PC.ContactID, CT.ID, CT.PageTitle, C.FirstName, C.LastName, C.MainPhoneNO, C.EmailAddress
FROM ProductContacts PC
INNER JOIN Content CT
ON PC.ContentID = CT.ID
INNER JOIN Contacts C
ON PC.ContactID = C.ContactID
WHERE PC.GeoUnitID = @GeoUnitID
and C.ContactID in (select max(ContactID) from Contacts group by ContentID)
ORDER BY CT.PageTitle ASC

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tehprince
Starting Member

9 Posts

Posted - 2008-04-21 : 16:21:58

I would need the max, thanks for the help nr!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources