order by after full outer join

Question

I create the following table on http://sqlfiddle.com in PostgreSQL 9.3.1 mode:

CREATE TABLE t
(
  id serial primary key,
  m varchar(1),
  d varchar(1),
  c int
);

INSERT INTO t
(m, d, c)
VALUES
('A', '1', 101),
('A', '2', 102),
('A', '3', 103),
('B', '1', 104),
('B', '3', 105);

table:

| ID | M | D |   C |
|----|---|---|-----|
|  1 | A | 1 | 101 |
|  2 | A | 2 | 102 |
|  3 | A | 3 | 103 |
|  4 | B | 1 | 104 |
|  5 | B | 3 | 105 |

From this I want to generate such a table:

| M | D |     ID |      C |
|---|---|--------|--------|
| A | 1 |      1 |    101 |
| A | 2 |      2 |    102 |
| A | 3 |      3 |    103 |
| B | 1 |      4 |    104 |
| B | 2 | (null) | (null) |
| B | 3 |      5 |    105 |

but with my current statement

select * from
  (select * from
    (select distinct m from t) as dummy1,
    (select distinct d from t) as dummy2) as combi
  full outer join
    t
  on combi.d = t.d and combi.m = t.m

I only get the following

| M | D |     ID |      C |
|---|---|--------|--------|
| A | 1 |      1 |    101 |
| B | 1 |      4 |    104 |
| A | 2 |      2 |    102 |
| A | 3 |      3 |    103 |
| B | 3 |      5 |    105 |
| B | 2 | (null) | (null) |

Attempts to order it by m,d fail so far:

select * from
  (select * from
    (select * from
      (select * from
        (select distinct m from t) as dummy1,
        (select distinct d from t) as dummy2) as kombi
      full outer join
        t
      on kombi.d = t.d and kombi.m = t.m) as result)
order by result.m

Error message:

ERROR: subquery in FROM must have an alias: select * from (select * from (select * from (select * from (select distinct m from t) as dummy1, (select distinct d from t) as dummy2) as kombi full outer join t on kombi.d = t.d and kombi.m = t.m) as result) order by result.m

It would be cool if somebody could point out to me what I am doing wrong and perhaps show the correct statement.

Answer 1

I think your problem is the order. You can solve this problem with the order by clause:

select * from
  (select * from
    (select distinct m from t) as dummy1,
    (select distinct d from t) as dummy2) as combi
  full outer join
    t
  on combi.d = t.d and combi.m = t.m
order by combi.m, combi.d

You need to specify which data you would like to order. In this case you get back the row from the combi table, so you need to say that. http://sqlfiddle.com/#!15/ddc0e/17

Answer 2

You could also use column numbers instead of names to do the ordering.

select * from
  (select * from
    (select distinct m from t) as dummy1,
    (select distinct d from t) as dummy2) as combi
  full outer join
    t
  on combi.d = t.d and combi.m = t.m
order by 1,2;

| M | D |     ID |      C |
|---|---|--------|--------|
| A | 1 |      1 |    101 |
| A | 2 |      2 |    102 |
| A | 3 |      3 |    103 |
| B | 1 |      4 |    104 |
| B | 2 | (null) | (null) |
| B | 3 |      5 |    105 |

Answer 3

I think you need another correlation name - dummy3? - after 'as result )' before the order by.

Answer 4

select * from
  (select kombi.m, kombi.d, t.id, t.c from
    (select * from
      (select distinct m from t) as dummy1,
      (select distinct d from t) as dummy2) as kombi
     full outer join t
  on kombi.d = t.d and kombi.m = t.m) as result
order by result.m, result.d

Answer 5

you just need a pivot table

the query is very simple

select classes.M, p.i as D, t.ID, t.C
from (select M, max(D) MaxValue from t group by m) classes
inner join pivot p
on p.i =< classes.MaxValue
left join t
on t.M = classes.M
and t.D = p.i

pivot table is a dummy table some how

CREATE TABLE Pivot (
 i INT,
 PRIMARY KEY(i)
)

populate is some how

CREATE TABLE Foo(
i CHAR(1)
)

INSERT INTO Foo VALUES('0')
INSERT INTO Foo VALUES('1')
INSERT INTO Foo VALUES('2')
INSERT INTO Foo VALUES('3')
INSERT INTO Foo VALUES('4')
INSERT INTO Foo VALUES('5')
INSERT INTO Foo VALUES('6')
INSERT INTO Foo VALUES('7')
INSERT INTO Foo VALUES('8')
INSERT INTO Foo VALUES('9')

Using the 10 rows in the Foo table, you can easily populate the Pivot table with 1,000 rows. To get 1,000 rows from 10 rows, join Foo to itself three times to create a Cartesian product:

INSERT INTO Pivot
SELECT f1.i+f2.i+f3.i
FROM Foo f1, Foo F2, Foo f3

you can read about that in Transac-SQL Cookbook by Jonathan Gennick, Ales Spetic

Answer 6

You just need to order by the final column definitions. t.m and t.d. SO your final SQL would be...

SELECT * 
FROM   (SELECT * 
        FROM   (SELECT DISTINCT m FROM   t) AS dummy1, 
               (SELECT DISTINCT d FROM   t) AS dummy2) AS combi 
       FULL OUTER JOIN t 
                    ON combi.d = t.d 
                       AND combi.m = t.m 
ORDER  BY t.m, 
          t.d; 

Also for query optimization perspective, it is better to now have many layers of sub queries.