Perform a double sort on two columns in SQL Server with a primary sort not in the evaluation of the first two

Question

I have a table using SQL Server 2008 it has a table with two sortable columns on it one is manually set and the other is calculated by a system procedure (this procedure sorts everything as a whole and assigns a sort starting at 10 until the highest row number times 10)

ID    Manual     System
------------------------
1      null      300
2      2         380
3      null      500
4      null      200

And I am trying to get it to sort the ids to be 4,2,1,3

I would like the output to take the Manual Sort over the System when it has been applied. to further complicate things if another row is added and it has a manual sort that also needs to be considered.

ID    Manual     System
-----------------------
1      null      300
2      2         380
3      null      500
4      null      200
5      5         100

so the new sort would be 4,2,1,3,5

ID    Manual     System
-----------------------
4      null      200
2      2         380
1      null      300
3      null      200
5      5         100

Any ideas? and I have tried Rank, Dense_Rank, Row_Number etc.

The solutions that have been given seem correct for my example. I forgot to mention there is a third column personID that is also a factor here.

ID    Manual     System    PersonID
-------------------------------------
4      null      200         22
2      2         380         22
1      null      300         22
3      null      200         22
5      5         100         22
8      1         210         25
6      1         480         25
7      null      600         25
9      4         800         25
10     null      990         25

So I first have to order them by person then, order them by Manual then by sort. which still seems to give me an issue.

Answer 1

Here is my solution: http://sqlfiddle.com/#!3/a32a0/1/0

SELECT *
FROM
(
  SELECT
    ID
    , ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY System)-.1 AS rn
    , Manual
    , System
    , PersonID
  FROM YourTable
) t0
ORDER BY PersonID
  , COALESCE(Manual, RN)

Here is the explanation:

• We are taking the row number as the base row number. But since we first order by a higher-order index of PersonID, I PARTITION BY... before I ORDER BY... this resets the index for each grouping of MANUAL
• In the case of a tie between the natural ordering of the ROW_NUMBER and the MANUAL sorting, I subtract .1 (arbitrary amount between (0,1)). This gives preference to the MANUAL value in case of a tie
• When it comes to ordering the final result, I ORDER BY the PARTITION BY value first, ensuring the proper grouping first, then I order by the first non-null value of MANUAL and RN

Give it a try. +points to the starting points of the previous two answers. I used one of them as a starting point and re-wrote from there.

EDIT: Removed the subtraction of .1 and added a new ranking function which "tricks" the optimizer into preferring manual over rank. I have no idea if this holds up in all cases or if the optimizer will fail to give the results in this order under other circumstances, but I wanted to include the findings just in case they're helpful.

My updated query is as follows:

SELECT *
  FROM
  (
    SELECT
      ID
      , ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY System) AS rn
      , ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY Manual) AS rn_throwaway
      , Manual
      , System
      , PersonID
    FROM YourTable
  ) t0
ORDER BY PersonID
  , COALESCE(Manual, RN)

And examples of it in use are at http://sqlfiddle.com/#!3/1831d/55/0 and http://sqlfiddle.com/#!3/a32a0/9/0

Answer 2

If I'm understanding your requirements, you want to sort by the System column, unless the Manual column is supplied, and in which case, use that as the sort position instead? If so, then this should work for you using CASE and ROW_NUMBER:

SELECT Id, Manual, System
FROM (
  SELECT Id, 
    Manual, 
    System,
    ROW_NUMBER() OVER (ORDER BY Manual, System)  rn
  FROM YourTable) t
ORDER BY CASE WHEN Manual IS NULL THEN RN ELSE Manual END, COALESCE(Manual,RN+1)

SQL Fiddle Demo

Answer 3

I think this is what you need. It is bit difficult to explain.

Basically inserting not null manual values as row index (or row number) to the record list ordered by system.

FIDDLE DEMO

;with cte as (
    select id, manual,system,
           convert(decimal(10,1),row_number() over(order by system)) rn
    from t
    where manual is null 
    union all
    select id, manual,system, convert(decimal(10,1),manual-0.5) rn
    from t
    where manual is not null
)
select id,manual,system
from cte
order by rn


| ID | MANUAL | SYSTEM |
------------------------
|  4 | (null) |    200 |
|  2 |      2 |    380 |
|  1 | (null) |    300 |
|  3 | (null) |    500 |
|  5 |      5 |    100 |