Hi all,
I have a table like this:
I have a table like this:
Table1
idname comment
1test null
2test1 abc
3test1 def
4TEST2abc
5test3 abc
6test3 def
7TEST4abc
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:
the output should be like this:
idname comment
1test null
2test1 def
3TEST2abc
4test3 def
5TEST4abc
How can I write the query for this scenario?
Thanks,
Darma
Thanks,
Darma
Jignesh TrivediPosted Mar 2, 2015, 7:18 AM
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.name,t.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 tejaPosted Mar 2, 2015, 7:48 AM
darma tejaPosted Mar 2, 2015, 6:34 AM
Advance thanks, Darma
Jignesh TrivediPosted Mar 2, 2015, 6:23 AM
In that case you have use any other Unique value instead of Id
hope this will help you.
darma tejaPosted Mar 2, 2015, 6:18 AM
If I do not have id column in table, than How can I do it?
Advance thanks, Darma
Jignesh TrivediPosted Mar 2, 2015, 5:23 AM
just forgot requirement :1
try
select ROW_NUMBER()over(order by id) id,name, 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 tejaPosted Mar 2, 2015, 3:44 AM
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:
Jignesh TrivediPosted Mar 1, 2015, 11:58 PM
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.