# Sorting a table according to another table

I have a table A.

```|----+----|
| P  |  S |
|----+----|
| p1 |  1 |
| p2 |  7 |
| p3 | 14 |
| p4 | 23 |
| p5 |  1 |
|----+----|
```

and table B

```|----+----|
|  S |  C |
|----+----|
|  1 | 21 |
|  5 | 21 |
| 23 | 21 |
|  1 | 30 |
|  7 | 90 |
|----+----|
```

I need to sort table A.

Sorting order:

```A. TableA.S exists where  (TableA.S = TableB.S) and TableB.C = 21
B. TableA.P
```

Final Output:

```|----+----|
| P  |  S |
|----+----|
| p1 |  1 |
| p4 | 23 |
| p5 |  1 |
| p2 |  7 |
| p3 | 14 |
|----+----|
```

This needs to be converted to DBIx::Class query.

I tried following:

```Select tableA.P, tableA.S
from tableA left join tableB on tableA.S = tableB.S
where (tableB.C = 21 or tableB.C is NULL)
order by tableB.C, tableA.P
```

But I am missing somewhere to get the result where tableB.S is '7'.

Thanks.

This sorts by A.P for all the records in A a matching record is found in B then by A.P for the other records.

``` SQL> select A.P
, A.S
from A
left join B
on ( A.S = B.S
and B.C = 21)
order by nvl2(B.S, 'ZZZZZZZZZZ', A.P), A.P
/

2    3    4    5    6    7    8
P           S
-- ----------
p1          1
p4         23
p5          1
p2          7
p3         14

SQL>
```

You can actually avoid the use of CASE or related equivalents (such as COALESCE(...) and the db-specific related functions):

```SELECT TableA.p, TableA.s
FROM TableA
LEFT JOIN TableB
ON TableA.s = TableB.s
AND TableB.c = 21
ORDER BY TableB.c, TableA.p
```

Why does this work? Default behavior of Oracle (and some other dbs) is that null sorts as the "greatest" value (some consider it the "lowest" value, which would be trivial to fix in this case). So first rows with a value in the given column (which is a constant value) are given, then those without...