Bobcat88 Bobcat88 - 1 month ago 17
SQL Question

Sorting in SQL w/ multiple conditions

I'm new to SQL, how would I go along with sorting the following:

I have three different type of dev projects(type 1,2, and 3). Type 1 and 2 also have an analysis that is linked with them. The analysis and dev type 1 and 2 will have a linking parent id, how would I sort with these specs:

Process Items in the following sequence


  • Dev type 3 projects

  • Dev type 1 or 2 projects combined with their analysis item (dev item columns take precedence analysis item columns)

  • Remaining analysis projects with no related dev projects



*EDIT* the lower ParentID should come first after these three specs

Example columns of the table are:

|ParentID| AnalysisItemID | DevItemId | DevType |
1 Null 2 1
4 5 Null Null
6 8 Null Null
8 Null 9 3
6 Null 7 2
1 3 Null Null


Sorted Output would be:

|ParentID| AnalysisItemID | DevItemId | DevType |
8 Null 9 3
1 Null 2 1
1 3 Null Null
6 Null 7 2
6 8 Null Null
4 5 Null Null

Answer

To me, it looks like you want to sort the data by the parent id, with the ordering based on the maximum devtype for the parent.

For this, you need to join to a summary table to get the information for the order by:

select t.*
from t join
     (select parentId, max(devtype) as maxdt
      from t
      group by parentId
     ) p
     on t.parentId = p.parentId
order by (case when maxdt = 3 then 1
               when maxdt in (1, 2) then 2
               else 3
          end), parentId, devtype desc