Tricky sort | Data engineer | SQL Problem Statement | SQL Quiz

[@saisai-st1jv]

select id from (select id, row_number() over(order by id asc) from tricky_sort where id>=0 union select id, row_number() over(order by id desc) + (select count(1) from tricky_sort where id>=0 ) from tricky_sort where id

[@DataArchitectStudio]

Good... understand your approach.. think any other way to solve this..

[@HARSHRAJ-gp6ve]

with cte as(
select numbers.*,ROW_NUMBER()OVER() as x1,'A' as val FROM numbers where ID>=0 ORDER BY ID
),cte1 as(
select numbers.*,ROW_NUMBER()OVER() as x2,'b' as val FROM numbers where ID

[@DataArchitectStudio]

@HARSHRAJ-gp6ve good but always think for simple solution.
Hint: CASE WHEN statement comes handy in these scenarios.

[@venkatakamaiah46]

select id from
(select id,1 as num from num_order where id>=0
union
select id,2 as num from num_order where id

[@DataArchitectStudio]

Orderby doesn't work in union/sub queries