T-SQL - get data based on two columns

Question

I want to create the following snapshot from below:

Input from the view ( Select * from test ). The goal is to get all the data in which the progress column contains the text tbd and the number counter is 1.

Can this be solved with the case argument?

Since sqlfiddle does not work here scheme:

 CREATE TABLE test ( [ID] [int] NOT NULL, [Counter] [int] NOT NULL, [Name] nvarchar(200) NULL, [Progress] nvarchar(200) NOT NULL ) INSERT INTO test VALUES (1, 1, 'userA', 'tbd'), (1, 2, 'userB', 'done'), (1, 3, 'userC', 'tbd'), (2, 1, 'userB', 'done'), (2, 5, 'userA', 'tbd'), (3, 1, 'userD', 'tbd'), (3, 2, 'userA', 'done'), (3, 7, 'userC', 'tbd'), (3, 11, 'userB', 'tbd') 

I could not get it to work.

I hope you help me.

Thank you so much.

Answer 1

Using the Exists clause, you can achieve the desired result.

Query

 SELECT * FROM test t WHERE EXISTS (SELECT 1 FROM test WHERE t.ID = ID AND progress = 'tbd' AND counter = 1) 

Result

 ID Counter Name Progress ----------------------------- 1 1 userA tbd 1 2 userB done 1 3 userC tbd 3 1 userD tbd 3 2 userA done 3 7 userC tbd 3 11 userB tbd 

And another alternative solution is simple SELF JOIN like this -

Query

 SELECT le.ID, le.Counter, le.Name, le.Progress FROM test le INNER JOIN test re ON le.ID = re.ID WHERE re.progress = 'tbd' AND re.counter = 1 

Above the query returns the same result.

Answer 2

Just try:

 select * from test where progress = 'tbd' and counter = 1 

Answer 3

Perhaps this will help you:

 select *, row_number() over(partition by Progress order by Counter) rowID into #tmp from Test select * from #tmp where ID in( select ID from #tmp where Counter = 1 and Progress = 'tbd' ) order by ID, rowID