buttonMASTER
asked on
Writing a sorting query involving two tables
I have two tables that looks like this:
The values in that Data_table are:
The values in the Sort_Table are:
Where the reference ID refers to the ID in the data_table.
I need a query to get the IDs from the Data_table in the order defined in the sort_table, but if anything from the data_table doesn't show up in the sort_table I want it to show up at the end like this:
Data_Table
Name Type
------------------ ------------------
ID NUMBER
Data DOESN'T_MATTERSort_Table
Name Type
------------------ ------------------
ID NUMBER
Reference_ID NUMBER
Order NUMBERThe values in that Data_table are:
ID data
----------------------------------------------------
1 value_1
2 value_2
3 value_3
4 value_4
5 value_5The values in the Sort_Table are:
ID reference_id order
----------------------------------------------------
1 2 2
2 3 1
3 5 3Where the reference ID refers to the ID in the data_table.
I need a query to get the IDs from the Data_table in the order defined in the sort_table, but if anything from the data_table doesn't show up in the sort_table I want it to show up at the end like this:
3
2
5
1 -- this isn't in sort_table
4 -- this isn't in sort_tableASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Great!
Please note the NULL value can be sorted differently depending on SQL engine used. If values missing in the Sort_table are at the top you may use different expression in ORDER BY, e.g.:
ORDER BY -s.order DESC, d.ID
Also the [order] column name must be enclosed in brackets in some SQL engines...
Please note the NULL value can be sorted differently depending on SQL engine used. If values missing in the Sort_table are at the top you may use different expression in ORDER BY, e.g.:
ORDER BY -s.order DESC, d.ID
Also the [order] column name must be enclosed in brackets in some SQL engines...
ASKER