Hi All, I have a table such as ![alt text] My expected result is Kindly let me know which is the best way to come to this result. switch case or row number ? ![alt text] : /storage/temp/1352-untitled.png : /storage/temp/1353-untitled.png
EDIT Adding some more data into the table (now at 20000 rows), the second query is clearly more efficient. Profiler says the first query uses four times the CPU of the second query. That's after adding indexes on country and parentid. Without indexes on those, also the number of reads differ a lot. But with the indexes, the reads are the same. -- First thing that comes to mind is this: with cte as (select *,row_number() over(partition by country order by parentid desc) as rownum from countries) select country,case when parentid is null then 'NO' else 'YES' end from cte where rownum=1 order by id Another option is this: select case when max(parentid) is null then 'NO' else 'YES' end as HasParent,country from countries group by country I have filled the table with some 500 rows, and it looks on STATISTICS IO as they do more or less the same in terms of reads. I haven't looked deeper into it. The query plan for the second query looks simpler, but that dosn't mean it's necessarily faster.