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 1,null union all select 1,null union all select 2,9999778876 union all select 3,7766887765 union all select 4,3344556765 union all select 4,null union all select 5,8899775544 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