query for filtering based on condition

Question

Hi I have data following like this Col1 Col2 Col3 Col4 Col5 Col7 1 19 test1 NY F NULL 1 19 test1 NY F NULL 2 87 test2 NY F 9999778876 3 92 test3 NY F 7766887765 4 70 test4 NY M NULL 4 70 test4 NY M 3344556765 5 22 test5 HI M 8899775544 I want to display distinct data For eg Col1 has some multiple records and I need to display any one record when Col7 is null. If Col7 is not null then I have to display record in which col7 is not null as follows Col1 Col2 Col3 Col4 Col5 Col7 1 19 test1 NY F NULL 2 87 test2 NY F 9999778876 3 92 test3 NY F 7766887765 4 70 test4 NY M 3344556765 5 22 test5 HI M 8899775544 How to achieve this query

Manish Kumar · Answer

Result is coming as per your given output

Ajay Mavatkar · Answer

it is displaying the records which is having null value I want to display records in which Col7 is not null

Manish Kumar · Answer

HI Ajay Try this declare @tbl table (col1 int col7 bigint) insert into @tbl select 1null union all select 1null union all select 29999778876 union all select 37766887765 union all select 43344556765 union all select 4null union all select 58899775544 select * from @tbl ;with cte as ( select row_number()over(partition by col1 order by col1) as cnt* from @tbl ) select * from cte where cnt=1 order by cnt


Col1	Col2	Col3	Col4	Col5	Col7
1	19	test1	NY	F	NULL
1	19	test1	NY	F	NULL
2	87	test2	NY	F	9999778876
3	92	test3	NY	F	7766887765
4	70	test4	NY	M	NULL
4	70	test4	NY	M	3344556765
5	22	test5	HI	M	8899775544