sql query

Question

Hi all I have a table like this: Table1 id name comment 1 test null 2 test1 abc 3 test1 def 4 TEST2 abc 5 test3 abc 6 test3 def 7 TEST4 abc I would like to get all rows 1. where comment='null' 2. if name (Example: test1) has 'abc' and 'def' in comment column than show only where comment= 'def' 3. if name has only 'abc' than show that row the output should be like this: id name comment 1 test null 2 test1 def 3 TEST2 abc 4 test3 def 5 TEST4 abc How can I write the query for this scenario? Thanks Darma

Jignesh Trivedi · Answer

oK USING rOW_NUMBER WITH PARTITION BY query you can get unique recored cobination with name try following query select name comment from #table1 where comment is null UNION select t.namet.comment from ( select Row_Number() over (partition by name order by name) id c.name c.comment from #table1 c) t inner join ( select MAX(B.Id) as id name from ( select Row_Number() over (partition by name order by name) Id name from #table1 where comment is not null) B group by B.Name) as a on t.id=a.Id and t.name = a.name hope this will help you.

darma teja · Answer

Great Jignesh Thanks it works!

darma teja · Answer

Unfortunately I do not have any unique id in my table Is there other possibility to get? Advance thanks Darma

Jignesh Trivedi · Answer

Hi In that case you have use any other Unique value instead of Id hope this will help you.

darma teja · Answer

Thanks Jignesh If I do not have id column in table than How can I do it? Advance thanks Darma

Jignesh Trivedi · Answer

Ok just forgot requirement :1 try select ROW_NUMBER()over(order by id) idname comment from( select Id name comment from #table1 where comment is null Union select t.id t.name t.comment from #table1 t inner join ( select Max(id) as Id from #table1 where comment is not null Group by name) as a on t.id=a.Id) A Please note that here I have use #table1 is temp table it must replace with original table name. hope this will help you.

darma teja · Answer

Hi Jignesh Thanks allot for your code. Unfortunately your sql query is not working. I will explain you again: 1. I would like to get all rows where comment = null 2. For example I have two rows for name = test1 comment = abc and comment = def. it means that if name has many rows than show only the row where comment = 'def'. 3 test1 def I need the following output: id name comment 1 test null 2 test1 def 3 TEST2 abc 4 test3 def 5 TEST4 abc

Jignesh Trivedi · Answer

hi try Create table #table1 ( id int name varchar(50) comment varchar(50) ) Insert into #table1 values (1'test'null) (2'test1''abc') (3'test1''def') (4'TEST2''abc') (5'test3''abc') (6'test3''def') (7'TEST4''abc') select ROW_NUMBER()over(order by t.id) id t.name t.comment from #table1 t inner join ( select Max(id) as Id from #table1 where comment is not null Group by name) as a on t.id=a.Id hope this will help you.