au2001 au2001 - 5 months ago 8
SQL Question

Select n latest rows between two SQLite tables

I have two tables A and B in an SQLite database. Table A looks like this:

| id | name | description | created |
|------|---------------|---------------|--------------|
| 1 | "Something" | "Something" | 1466266963 |
| 2 | "Something" | "Something" | 1466266965 |
| 3 | "Something" | "Something" | 1466266967 |
| 4 | "Something" | "Something" | 1466266969 |
| 5 | "Something" | "Something" | 1466266971 |
| 6 | "Something" | "Something" | 1466266975 |


And the table B is like that:

| id | title | content | created |
|------|---------------|---------------|--------------|
| 1 | "Something" | "Something" | 1466266951 |
| 2 | "Something" | "Something" | 1466266953 |
| 3 | "Something" | "Something" | 1466266954 |
| 4 | "Something" | "Something" | 1466266956 |
| 5 | "Something" | "Something" | 1466266957 |
| 6 | "Something" | "Something" | 1466266978 |


And I want to get the 4 oldest between the two tables.

So in this example I would get something like that:

| id | title | name | content | description | created |
|------|---------------|---------------|---------------|---------------|--------------|
| 6 | "Something" | | "Something" | | 1466266975 |
| 6 | | "Something" | | "Something" | 1466266975 |
| 5 | | "Something" | | "Something" | 1466266975 |
| 4 | | "Something" | | "Something" | 1466266975 |


I tried
SELECT A.*, B.* FROM A, B ORDER BY created DESC LIMIT 4
but I get an empty result (my tables aren't empty though).

I also looked at JOINs but didn't manage to get anything working.

Any idea? Thanks.

Answer

Use union all

select * from(
select * from a
union all
select * from b
) t
order by created DESC LIMIT 4
Comments