Sort records from different tables

Question

I have two table with an column 'minute'. These table are joined with a INNER JOIN. Now I want to print out all the records from both tables sorted on both the columns 'minute'

Example

Table 1: name - minute

 John - 1
 Marc - 3

Table 2: name - minute

Gareth - 2
Joe - 3

Output:

John, Gareth, Marc, Joe

The two tables have to remain two separate tables.

You can check http://stackoverflow.com/questions/4069784/order-by-in-inner-join — sujikin, Jul 18 '13 at 11:53

score 5 · Accepted Answer · answered Jul 18 '13 at 12:03

5

You should use UNION :-

 (SELECT  *
 FROM    table1)
    UNION 
 (SELECT  *
 FROM    table2)

 ORDER BY minute ASC

Output:-

john    1
gareth  2
marc    3
joe     3

answered Jul 18 '13 at 12:03

Vivek Sadh

4,230
3
32
49

bvr · Answer 2 · 2013-07-18T12:11:44.727

Try this

SELECT name
FROM
(
  SELECT name, [minute],1 AS sort FROM Table1
  UNION ALL 
  SELECT name, [minute],2 AS sort FROM Table2
) T
ORDER BY [minute],sort

output

name    minute
John    1
Gareth  2
Marc    3
Joe     3

OR

SELECT name, [minute]
FROM
(
  SELECT name, [minute] FROM Table1
  UNION ALL 
  SELECT name, [minute] FROM Table2
) T
ORDER BY [minute]

output

name    minute
John    1
Gareth  2
Joe     3
Marc    3

score 0 · Answer 3 · answered Jul 18 '13 at 12:01

0

Try this:

Select name
From table1
Union all
Select name
From table2
order by minute

answered Jul 18 '13 at 12:01

Karloss

817
3
9
27

Liam Sorsby · Answer 4 · 2013-07-18T12:01:28.190

-1

Not sure if this is what you are looking for

SELECT name, minute FROM table1, table2 ORDER BY table1.minute,table2.minute ASC;

edited Jul 18 '13 at 12:01

answered Jul 18 '13 at 11:49

Liam Sorsby

2,912
3
28
51

didn't think if you used a join you would be able to get two name fields if they are both called join – Liam Sorsby Jul 18 '13 at 12:00

Sort records from different tables

4 Answers4