Get all rows from table and sort them by timestamp-CodePudding

I have these test tables which I would like to select and combine the result by timestamp:

create table employees
(
    id              bigint primary key,
    account_id      integer,
    first_name      varchar(150),
    last_name       varchar(150),
    timestamp       timestamp
);

create table accounts
(
    id               bigint primary key,
    account_name     varchar(150) not null,
    timestamp        timestamp
);

create table short_name
(
    account_id       bigint primary key,
    full_name     varchar(150) not null
);

INSERT INTO short_name(account_id, full_name)
VALUES(1, 'city 1');

INSERT INTO short_name(account_id, full_name)
VALUES(2, 'city 2');

INSERT INTO employees(id, account_id, first_name, last_name, timestamp)
VALUES(1, 1, 'Donkey', 'Kong', '10-10-10');

INSERT INTO employees(id, account_id, first_name, last_name, timestamp)
VALUES(2, 2, 'Ray', 'Kurzweil', '11-10-10');

INSERT INTO employees(id, account_id, first_name, last_name, timestamp)
VALUES(32, 2, 'Ray2', 'Kurzweil2', '1-10-10');

INSERT INTO employees(id, account_id, first_name, last_name, timestamp)
VALUES(33, 2, 'Ray3', 'Kurzweil3', '2-10-10');

INSERT INTO employees(id, account_id, first_name, last_name, timestamp)
VALUES(3432, 3, 'Percy', 'Fawcett', '6-10-10');

INSERT INTO accounts(id, account_name, timestamp)
VALUES(1, 'DK Banana Account', '5-10-10');

INSERT INTO accounts(id, account_name, timestamp)
VALUES(2, 'Kurzweil''s invetions moneyz baby!', '10-10-10');

INSERT INTO accounts(id, account_name, timestamp)
VALUES(3, 'Amazonian Emergency Fund', '10-10-10');


select *, e.timestamp, sn.full_name from employees e
    INNER JOIN short_name as sn on sn.account_id = e.id
union all
select *, a.timestamp from accounts a
where timestamp >= '2022-03-25T13:00:00'
and timestamp < '2022-04-04T13:00:00'
AND timestamp IS NOT NULL
order by timestamp;

https://www.db-fiddle.com/f/pwzwQTsHuP27UDF17eAQy4/36

How I can select the tables and display a combined table rows ordered by timestamp? The problem is that I have a different number of table columns and I would like to display them also and globally to sort all rows by timestamp.

CodePudding user response：

SELECT T.Col1, T.Col2 FROM ( SELECT Col1,Col2 FROM Table 1

UNION ALL

SELECT Col1,Col2 FROM Table 2

UNION ALL ..... and so on )T ORDER BY T.TimeStamp

Note: Combine all the tables into a single select statement and order by at last.

------- OR ------

Insert all the tables data into a single temp table and use it.

CodePudding user response：

SELECT 'employee' as type, e.id, e.timestamp, sn.full_name 
FROM employees e
INNER JOIN 
short_name as sn on sn.account_id = e.id
UNION ALL
SELECT 'account' as type, a.id, a.timestamp, '' as short_name 
FROM 
accounts a
WHERE timestamp IS NOT NULL
ORDER BY timestamp;

You can use dummy-columns to make the column-count fitting. To show the concept, I dropped the timestamp-filter. You can of course re-add it and also select additional columns (but of course then you also have to use some more dummy-columns)

CodePudding user response：

I am unsure what you try to achieve. but you have to "pad" the number of columns missing, but the second query doesn't have any rpws so you don't see it in you fiddle

select *, e.timestamp, sn.full_name from employees e
    INNER JOIN short_name as sn on sn.account_id = e.id    
Union all
select null,null,null,null,null,NULL,NULL, a.timestamp,'' 
from accounts a
where timestamp >= '2022-03-25T13:00:00'
and timestamp < '2022-04-04T13:00:00'
AND timestamp IS NOT NULL
order by 8;