CODEWITHSUNDEEP
sqlpostgresqlsqlfiddle
Tobias Hermann
Tobias Hermann

Reputation: 10956

order by after full outer join

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.

Upvotes: 1

Views: 2057

Answers (6)

SpartanElite
SpartanElite

Reputation: 624

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.

Upvotes: 1

Carlos Cocom
Carlos Cocom

Reputation: 932

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

Upvotes: 1

rslemos
rslemos

Reputation: 2730

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

Upvotes: 2

simon at rcl
simon at rcl

Reputation: 7344

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

Upvotes: 0

user1339920
user1339920

Reputation:

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 |

Upvotes: 1

PeterRing
PeterRing

Reputation: 1797

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

Upvotes: 1

Related Questions