Duplicate values in a query - 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 2008 Forums

Transact-SQL (2008)

Duplicate values in a query

Author

Topic

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-04-06 : 03:53:10

The following query generates dups beacuse j.impact_level column can return several values based on f.TICKETNO.
Ex.
j.impact_level TICKETNO
P1/S3 22626559
P1/S1 22626559
P1/S2 22626559
P2 22626559
P2 12227778
P1/S2 12227778
null 12227778
null 13225700

How can I get only 1 unique record per this condition knowing j.impact_level column can return also nulls (which I want to include to get all records with no values in j.impact_level column), By unique record I mean only 1 instance of each ticket TICKETNO
is displayed.
desired result (the idea is to show value from j.impact_level based on its highest priority of a ticket, Hghest priority is P1/S1,then P1/S2, then P1/S3 and P2, P3 and so on):

j.impact_level TICKETNO
P1/S1 22626559
P1/S2 12227778
null 13225700

current query:

SELECT distinct f.TICKETNO,
f.[STATUS],
f.OPEN_TIME,
f.P_CODE,
f.S_CODE,
f.ASSIGNMENTTEAM,
f.CONTACT_NAME,
j.impact_level,
f.OPEN_MONTH,
f.TEAM
FROM TICKETS f
LEFT JOIN IMPLOB1 j
ON f.TICKETNO=j.TICKETNO
ORDER BY f.TICKETNO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-06 : 15:22:35

[code]
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY f.TICKETNO ORDER BY COALESCE(j.impact_level,'')) AS Rn,
f.TICKETNO,
f.[STATUS],
f.OPEN_TIME,
f.P_CODE,
f.S_CODE,
f.ASSIGNMENTTEAM,
f.CONTACT_NAME,
j.impact_level,
f.OPEN_MONTH,
f.TEAM
FROM TICKETS f
LEFT JOIN IMPLOB1 j
ON f.TICKETNO=j.TICKETNO
)t
WHERE Rn=1
ORDER BY TICKETNO
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nietzky
Yak Posting Veteran

75 Posts

Posted - 2012-04-06 : 16:06:37

Thank you very much. You are the best. Works just fine.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-06 : 16:08:08

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources