Select row if exists or another

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)

Select row if exists or another

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2014-01-27 : 09:43:59

Really not sure how to approach this problem, maybe a group by....

Would like to bring back only one row for a certain situation.

A field called "Name" has four entries.
Each entry has a different status, 0, 1, 2, 3
If the field "Status" has an entry of 2, only return that one.
If there is not a 2, then return 1.
If there is not a 2 or a 1, then return 3.
And if there is not a 2, 1, or 3, return the row with a status of 0.

Suggestions?

Thanks...

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-01-27 : 10:37:16

[code]
-- *** Test Data ***
CREATE TABLE #t
(
[Name] varchar(20) NOT NULL
,[Status] tinyint NOT NULL
);
INSERT INTO #t
VALUES ('Name1', 0),('Name1', 1),('Name1', 2),('Name1', 3)
,('Name2', 0),('Name2', 1),('Name2', 3)
,('Name3', 0),('Name3', 3)
,('Name4', 0);
-- *** End Test Data ***

WITH NameOrder
AS
(
SELECT [Name], [Status]
,ROW_NUMBER() OVER (PARTITION BY [NAME]
ORDER BY CASE [Status] WHEN 2 THEN 1 WHEN 1 THEN 2 WHEN 3 THEN 3 ELSE 4 END) AS rn
FROM #t
)
SELECT [Name], [Status]
FROM NameOrder
WHERE rn = 1;
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources