how to maintain order while doing join in sql

Question

table 1 and table 2 have their primary key (key1,key2) to be the same. table 3 is a user defined table with one column field5 which is common to table2. I need to select the rows from table 1 and table 2 where field5 in table2 is in table3. I need to obtain the rows in the same order as table3.

table 1

key1 key2 field1 field2

table 2

key1 key2 field3 field4 field5

user defined table

field5

the query i am thinking is

select a.key1, a.key2, a.field1, a.field2, b.field3, b.field4
from table1 as a INNER JOIN table2 as b ON a.key1 = b.key1 AND a.key2 = b.key2
where b.field5 in (select field5 from table3)

this will not give me the rows in the same order as in table3.

I would need something like this to maintain the order?

select a.key1, a.key2, a.field1, a.field2, d.field3, d.field4 from
table1 as a INNER JOIN
 (select b.key1, b.key2, b.field3, b.field4
  from table2 as b INNER JOIN table3 as c
  ON b.field5 = c.field5) as d
WHERE a.key1 = d.key1 AND a.key2 = d.key2

Answer 1

Order is only offered in the final result when you use an order by clause.

If you want "in the same order as table3" you must write a proper order by clause to recreate the order that you have in table3.

The join is free to scramble rows if it makes things go faster.

Answer 2

If you would like to order output data, as it is ordered (inserted) in table3, I'd suggest, adding a column to table3 rowid int identity(1,1). This creates an autoincrement field, which will increase its value for each new row inserted.

Then you can select rows as they were inserted into that table as selecting order by table3.rowid

Reference from MSDN - IDENTITY (Property)

Answer 3

You cannot depend on the physical ordering of rows in a table. To ensure a given order, add a sort field to your table3, and change your query to

select 
  a.key1, 
  a.key2, 
  a.field1, 
  a.field2, 
  b.field3, 
  b.field4 
from table1 as a 
INNER JOIN table2 as b ON a.key1 = b.key1 AND a.key2 = b.key2 
INNER JOIN table3 as c on b.field5 = c.field5
ORDER BY c.sort_field

Answer 4

You need to add an ORDER BY clause.

SELECT a.key1, a.key2, a.field1, a.field2, d.field3, d.field4 
FROM table1 as a INNER JOIN (select b.key1, b.key2, b.field3, b.field4 from table2 as b 
INNER JOIN table3 as c ON b.field5 = c.field5) as d 
WHERE a.key1 = d.key1 AND a.key2 = d.key2
ORDER BY c.field5;

EDIT:

I misunderstood your original intent. My solution sorts on the "value" for field5, which likely isn't the order they are laid out in the table. You can not rely on the physical ordering of rows within a table and I'm afraid that what you're trying to do isn't something you should be relying on. The reason, I don't think you could ever guarantee the order will match the "same order as table3" if what your after is physical order unless the table was clustered on field5.

For example, if the table was clustered on field1 and you use an identity or "sorting" column for sorting as others suggest, I would expect the sort on the identity/sorting column to be far different from the physical order of the rows in the table. I'm not a SQL Server expert by any means, but many databases allow row movement as well, which could change the "order" the rows are physically stored without changing the values within your sort columns, thus breaking your rules again.

I'm not certain as to why you need these returned in this strict of an order. If possible, I would eliminate that constraint and allow the data to be processed/handled by sorting the value of a column whether it be field5 or some other field.

Answer 5

Give a try on this:

select a.key1, a.key2, a.field1, a.field2, b.field3, b.field4
from table1 as a 
    JOIN table2 as b ON a.key1 = b.key1 AND a.key2 = b.key2
    join (select field5, ROW_NUMBER() over (order by (select null)) Table3_Order from table3) c on b.field5 c.field5
order by c.Table3_Order

Table3_Order is the original (I think, the phisical) order in the table3.

Answer 6

I just had this same problem myself and came up with another solution -- you can create a temp table with whatever columns you need (starting with field5). Inserting all the "field5" values from table3 into your temp table first will lock in the order that you want (same order as they are in table3). Then you can update the rest of your temp table columns by joining on field5 etc. Something like this:

create table #tempTable(field5 varchar(200), key1 int, key2 int, field1 varchar(200), field2 varchar(200), field3 varchar(200), field4 varchar(200))
--insert field5 values (in order) into temp table's first column
insert into #tempTable
select field5,null,null,null,null,null,null from table3
--update all other columns in the temp table as needed by joining on field5 etc.
update temp
set key1 = b.key1,
    key2 = b.key2,
    field3 = b.field3,
    field4 = b.field4
from #tempTable temp
join table2 b on b.field5 = temp.field5
--add the final fields from table1 in the same way...
update temp
set field1 = a.field1,
    field2 = a.field2
from #tempTable temp
join table1 a on a.key1 = temp.key1

Answer 7

I had this problem and I solved it using ROWNUM in Oracle:

select a.key1, a.key2, a.field1, a.field2, b.field3, b.field4 from 
(select rownum as to_order, key1, key2, field1, field2 from table1) as a
INNER JOIN table2 as b
ON a.key1 = b.key1 AND a.key2 = b.key2 where b.field5 in (select field5 from table3)
order by a.to_order;