Writing a sorting query involving two tables

buttonMASTER
buttonMASTER used Ask the Experts™
on
I have two tables that looks like this:

Data_Table

Name               Type               
------------------ ------------------ 
ID                 NUMBER
Data               DOESN'T_MATTER

Open in new window


Sort_Table

Name               Type               
------------------ ------------------ 
ID                 NUMBER
Reference_ID       NUMBER
Order              NUMBER

Open in new window


The values in that Data_table are:

ID		data
----------------------------------------------------
1		value_1
2		value_2
3		value_3
4		value_4
5		value_5

Open in new window


The values in the Sort_Table are:

ID		reference_id	order
----------------------------------------------------
1		2		2
2		3		1
3		5		3

Open in new window


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:

3
2
5
1 -- this isn't in sort_table
4 -- this isn't in sort_table

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SELECT d.ID 
  FROM Data_table d
  LEFT JOIN Sort_Table s ON s.reference_id = d.ID
 ORDER BY s.order, d.ID

Open in new window

Author

Commented:
That worked. Thank you very much!
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...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial