CODEWITHSUNDEEP
sqlsql-server-2008t-sqlsql-server-2005
AJM
AJM

Reputation: 32490

SQL Server Ordering Based on two columns - parent and child

I have an ordering problem in SQL Server.

My result set is that of a primary column and a secondary column. The secondary column refers to child nodes of the primary column e.g. it is the id of a primary column that appears later on in the result set

Primary Column | Secondary Column
1                  NULL
1                  2
1                  3
3                  NULL
3                  NULL
2                  NULL
2                  NULL
2                  7

I need the SQL SELECT to be order by primary column but where there is a secondary column inject the rows related to that id under the primary column e.g. the above result set would look like. 

Primary Column | Secondary Column
1                  NULL
1                  2
2                  NULL
2                  NULL
2                  7
1                  3
3                  NULL
3                  NULL

Is this possible in SQL without using a CURSOR

Upvotes: 1

Views: 1527

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

One way to do this is with a self-join:

select primary, secondary
from (select t.primary, t.secondary,
             max(case when tsub.primary is not null then 1 else 0 end) as hasprimary
      from t left outer join
           t tsub
           on t.secondary = t.primary
      group by t.primary, t.secondary
     ) a
order by (case when hasprimary = 1 then secondary else primary end),
         hasprimary desc,
         secondary

This calculates which id to use first -- the primary or secondary -- based on whether there are matching rows. The second clause puts the "unusual" one first, followed by the rest.

Upvotes: 2

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

with cte as (select *,ROW_NUMBER() over(partition by Primary_Column order by isnull(Secondary_Column,0)) as rownum 
    from test_order
    where Secondary_Column is null or Secondary_Column not in (select Primary_Column from test_order)
), 
cte1 as (
    select *,ROW_NUMBER() over(partition by Secondary_Column order by isnull(Primary_Column,0))-1 as rownum
    from test_order
    where Secondary_Column  in (select Primary_Column from test_order)
),
cte2 as (select 'c1' [type],* from cte
    union all
    select 'c2' [type],* from cte1)
    select Primary_Column,Secondary_Column from cte2
    order by case when [type]='c1' then Primary_Column 
                                else Secondary_Column end,rownum

SQL Fiddle Demo

Upvotes: 2

Related Questions