Thej Kumar Thej Kumar - 3 months ago 7
SQL Question

SQL Query to order data based on other column value

I have the below set of data(current data), where system_id is the ID of the particular system. And pre_system_id's are ID of system where it is dependent. Now I need the order in such a way that rows with no dependent system should come first , then rows with one dependent system come second and so on.

The current result:

System_ID PRE_SYSTEM_ID1 PRE_SYSTEM_ID2 PRE_SYSTEM_ID3 PRE_SYSTEM_ID4
106 100
105
112 105 100 109
100
109 100 105
119 100 109 105 112
102 112 109
104 109 106


The actual result should be like below:

Order System_ID PRE_SYSTEM_ID1 PRE_SYSTEM_ID2 PRE_SYSTEM_ID3 PRE_SYSTEM_ID4
1 100
2 105
3 106 100
4 109 100 105
5 112 105 100 109
6 119 100 109 105 112
7 104 109 106
8 102 112 109 104


The query for the current result is simply

Select * from ImpactedSystem;

APC APC
Answer

Sorting by the various PRE_SYSTEM_IDn columns using the nulls first clause should produce the order you want:

select * 
from  ImpactedSystem
order by PRE_SYSTEM_ID1 nulls first, 
         PRE_SYSTEM_ID2 nulls first,  
         PRE_SYSTEM_ID3 nulls first,  
         PRE_SYSTEM_ID4 nulls first,
         SYSTEM_ID

Finally sort by SYSTEM_ID, to order the values with no dependent IDs.