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.

Answers


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...


Need Your Help

Concatenate SQL Server stored procedure result with additional text

sql-server stored-procedures concatenation

I'd like to take the result of a stored procedure and concatenate it with additional text in as simple a way as possible.

Good non-relational/quazi-relational DB with .NET API?

.net persistence nosql non-relational-database

Anyone have any recommendations for a non-relational/partially-relational DB with a .NET API?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.