need SQL help - 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 2012 Forums

Transact-SQL (2012)

need SQL help

Author

Topic

mukejee
Starting Member

5 Posts

Posted - 2014-02-11 : 04:04:05

Hi experts,

I am having a table as given below

id desc flag
1 abc 0
2 abc 1
3 xyz 0
4 cde 0

I want to write a SQL which will pull record with unique desc, also if there is a duplicate then u should get the record for which flag value is 1

here out put is

id desc flag
2 abc 1
3 xyz 0
4 cde 0

Thanks
MUS

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-11 : 04:39:05

[code]

;with aCTE
AS(
SELECT 1 as ID,'abc' as [desc],0 as [flag] union all
SELECT 2, 'abc', 1 union all
SELECT 3 ,'xyz',0 union all
SELECT 4,'cde', 0)

SELECT * from
(
SELECT
*
,ROW_NUMBER() OVER(PARTITION BY [DESC] ORDER BY [flag] desc) as rn
from aCTE)A
where A.rn=1

[/code]

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-11 : 04:39:28

the output


ID	desc	flag	rn
2	abc	1	1
4	cde	0	1
3	xyz	0	1

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb MCP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-12 : 00:02:18

[code]
SELECT *
FROM Table t
WHERE NOT EXISTS (SELECT 1
FROM table
WHERE desc = t.desc
AND flag > t.flag
)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources