Distinct - 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

General SQL Server Forums

New to SQL Server Programming

Distinct

Author

Topic

mukhan85
Starting Member

46 Posts

Posted - 2008-08-08 : 14:29:24

Hi, how can I get only unique rows for ch.Infrastructure_DI?
here is the query. The result still contains the same rows.

Thank you.

select distinct ch.Infrastructure_Change_ID,
inc.Request_ID01
FROM

((select Infrastructure_Change_ID
FROM Infrastructure
WHERE YEAR(DATEADD(second, Submit_Date, '1969-12-31 8:00:00 PM')) = YEAR(GETDATE()) AND
(Change_Request_Status = 11 OR Change_Request_Status = 10) AND
Product_Cat_Tier_1_2_ = 'Network' AND Product_Cat_Tier_2__2_ = 'RFC' AND
Product_Cat_Tier_3__2_ NOT IN ('Installation', 'Voice AND Video')) as ch LEFT OUTER JOIN

(SELECT DISTINCT (Request_ID02),
Request_ID01

FROM Association as inc

WHERE YEAR(DATEADD(second, Submit_Date, '1969-12-31 7:00:00 PM')) = YEAR(GETDATE()) AND
Request_ID01 LIKE 'INC%') AS inc ON (ch.Infrastructure_Change_ID = inc.Request_ID02))

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-08 : 14:29:56

Please show us sample data to illustrate your issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mukhan85
Starting Member

46 Posts

Posted - 2008-08-08 : 14:46:32

quote:
Originally posted by tkizer

Please show us sample data to illustrate your issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Here is sample result set:
CRQ000000022553 INC0011
CRQ000000022553 INC0022
CRQ000000022553 INC0012
CRQ000000022510 INC2212
CRQ000000022510 INC3221
AS you can see CRQ000000022553 appeared 3 times and I need to see it only once. with any of INC's . Thank you/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-08 : 14:48:06

Use a group by:

select ch.Infrastructure_Change_ID, MAX(inc.Request_ID01) AS Request_ID01
FROM
((select Infrastructure_Change_ID
FROM Infrastructure
WHERE YEAR(DATEADD(second, Submit_Date, '1969-12-31 8:00:00 PM')) = YEAR(GETDATE()) AND
(Change_Request_Status = 11 OR Change_Request_Status = 10) AND
Product_Cat_Tier_1_2_ = 'Network' AND Product_Cat_Tier_2__2_ = 'RFC' AND
Product_Cat_Tier_3__2_ NOT IN ('Installation', 'Voice AND Video')) as ch LEFT OUTER JOIN

(SELECT DISTINCT (Request_ID02),
Request_ID01

FROM Association as inc

WHERE YEAR(DATEADD(second, Submit_Date, '1969-12-31 7:00:00 PM')) = YEAR(GETDATE()) AND
Request_ID01 LIKE 'INC%') AS inc ON (ch.Infrastructure_Change_ID = inc.Request_ID02))
GROUP BY ch.Infrastructure_Change_ID

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources