Pls see the 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

General SQL Server Forums

New to SQL Server Programming

Pls see the query

Author

Topic

aoriju
Posting Yak Master

156 Posts

Posted - 2012-05-23 : 04:58:32

Hi all,

Please see the below query

DECLARE @tbl_TEST AS TABLE(col1 VARCHAR(50), col2 VARCHAR(50), col3 VARCHAR(50))
INSERT INTO @tbl_TEST
SELECT 'c1','n1','p1'
INSERT INTO @tbl_TEST
SELECT 'c2','n2','p2'
INSERT INTO @tbl_TEST
SELECT 'c3','n2','p3'
INSERT INTO @tbl_TEST
SELECT 'c4','n1','p4'
INSERT INTO @tbl_TEST
SELECT 'c5','n5','p5'

i need the output like c5,n5,p5
because col2 contains n1 & n2 are duplicate..

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2012-05-23 : 06:31:20

Try something like this:


SELECT T.col1, T.col2, T.col3
  FROM (SELECT T.col1, T.col2, T.col3, COUNT(*) OVER(PARTITION BY T.col2) AS cnt
          FROM @tbl_TEST AS T) AS T
 WHERE T.cnt = 1;

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-23 : 15:31:28

[code]
SELECT *
FROM table t
WHERE NOT EXISTS(SELECT 1
FROM table
WHERE col2 = t.col2
AND col1 <> t.col1
)
[/code]

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

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-23 : 17:13:39

[code]

DECLARE @tbl_TEST AS TABLE(col1 VARCHAR(50), col2 VARCHAR(50), col3 VARCHAR(50))
INSERT INTO @tbl_TEST
SELECT 'c1','n1','p1'
INSERT INTO @tbl_TEST
SELECT 'c2','n2','p2'
INSERT INTO @tbl_TEST
SELECT 'c3','n2','p3'
INSERT INTO @tbl_TEST
SELECT 'c4','n1','p4'
INSERT INTO @tbl_TEST
SELECT 'c5','n5','p5'

select * from @tbl_TEST

;with cte as
( select Count(col2) over(PARTITION by col2 ) rn,* from
@tbl_TEST)

select * from cte where rn = 1
[/code]

Vijay is here to learn something from you guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources