question

aRookieBIdev avatar image
aRookieBIdev asked

SQL QUERY HELP

Hi All, I have a table such as ![alt text][1] 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][2] [1]: /storage/temp/1352-untitled.png [2]: /storage/temp/1353-untitled.png
sql-server-2008-r2query-hint
untitled.png (2.9 KiB)
untitled.png (2.2 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thank you .
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.