Special TABLE sorting

Question

I have a two column TABLE from a previous query like this:

+--------+--------+
| Id_no1 | Id_no2 |
+--------+--------+
|  1     |    2   |
|  1     |    2   |
|  1     |    3   |
|  1     |    5   |
|  2     |    1   |
|  2     |    3   |
|  2     |    3   |
|  2     |    5   |
|  3     |    1   |
|  3     |    2   |
+--------+--------+

The TABLE is ORDER BY Id_no1, Id_no2

I want Id_no1 and Id_no2 to be paired like this:

+--------+--------+
| Id_no1 | Id_no2 |
+--------+--------+
|  1     |    2   |
|  1     |    2   |
|  2     |    1   |
|        |        |
|  1     |    3   |
|  3     |    1   |
|        |        |
|  1     |    5   |
|        |        |
|  2     |    3   |
|  2     |    3   |
|  3     |    2   |
|        |        |
|  2     |    5   |
+--------+--------+

Id_no1 and Id_no2 are shops, sender and recipient. I want to group exchange between all shops.

Answer 1

Your requirement is apparently to sort your data in order of the parties involved. To do that, you are trying to sort first by the lowest party involved (Ascending), then by the highest party involved (also Ascending).

To get this done, I wrote the following: this gives an output in the order you've shown, and will work ... assuming my wild guess is remotely connected to your question.

/* Sample Data */

    WITH Table1 AS 
      (
        SELECT ID_No1, ID_No2
        FROM 
          ( VALUES 
            (1,2),
            (1,2),
            (1,3),
            (1,5),
            (2,1),
            (2,3),
            (2,3),
            (2,5),
            (3,1),
            (3,2)
          ) AS VALUE(ID_No1, ID_No2)
      )
/* Query */
    SELECT ID_No1, ID_No2
    FROM 
      (
        SELECT 
            *,
            CASE WHEN ID_No1 > ID_No2 THEN ID_No1 ELSE ID_No2 END AS MaxParty,
            CASE WHEN ID_No1 < ID_No2 THEN ID_No1 ELSE ID_No2 END AS MinParty
        FROM Table1
      ) arr
    ORDER BY MinParty, MaxParty, ID_No1

Answer 2

May be this will help:

select * from mytab 
order by case when id_no1 < id_no2 then id_no1 else id_no2 end, 
         case when id_no2 > id_no1 then id_no2 else id_no1 end;

= order by min(col1, col2), max(col1, col2)