I´d like to merge somehow these MySQL 5.6 results. The idea is to get data from latest and oldest rows of each id. Pair time/id values are unique.
Table is:
| time | id | titulo | precio | vendidos |
+---------------------+----+----------+--------+----------+
| 2019-10-26 19:12:14 | 1 | apple_a | 2 | 10 |
| 2019-10-26 19:12:14 | 2 | pea | 3 | 7 |
| 2019-10-26 19:12:14 | 3 | orange_a | 1 | 4 |
| 2019-10-28 19:12:14 | 3 | orange_a | 2 | 12 |
| 2019-10-28 19:12:14 | 4 | banana | 5 | 7 |
| 2019-10-28 19:12:14 | 5 | peach | 9 | 1 |
| 2019-10-29 19:12:14 | 1 | apple_b | 2 | 12 |
| 2019-10-29 19:12:14 | 2 | pea | 3 | 9 |
| 2019-10-29 19:12:14 | 3 | orange_b | 2 | 19 |
| 2019-10-29 19:12:14 | 4 | banana | 6 | 14 |
| 2019-10-30 19:12:14 | 1 | apple_b | 3 | 17 |
| 2019-10-30 19:12:14 | 2 | pea | 3 | 11 |
With code:
-- Get latest rows for each id:
SELECT b.*
FROM (SELECT t.id, MAX(time) AS latest
FROM srapedpubs t GROUP BY id) a
INNER JOIN srapedpubs b ON b.id = a.id AND b.time = a.latest
ORDER BY id ASC
;
-- Get oldest rows for each id:
SELECT b.*
FROM (SELECT t.id, MIN(time) AS oldest
FROM srapedpubs t GROUP BY id) a
INNER JOIN srapedpubs b ON b.id = a.id AND b.time = a.oldest
ORDER BY id ASC
;
The result is:
| time | id | titulo | precio | vendidos |
|---------------------|----|----------|--------|----------|
| 2019-10-30 19:12:14 | 1 | apple_b | 3 | 17 |
| 2019-10-30 19:12:14 | 2 | pea | 3 | 11 |
| 2019-10-29 19:12:14 | 3 | orange_b | 2 | 19 |
| 2019-10-29 19:12:14 | 4 | banana | 6 | 14 |
| 2019-10-28 19:12:14 | 5 | peach | 9 | 1 |
| time | id | titulo | precio | vendidos |
|---------------------|----|----------|--------|----------|
| 2019-10-26 19:12:14 | 1 | apple_a | 2 | 10 |
| 2019-10-26 19:12:14 | 2 | pea | 3 | 7 |
| 2019-10-26 19:12:14 | 3 | orange_a | 1 | 4 |
| 2019-10-28 19:12:14 | 4 | banana | 5 | 7 |
| 2019-10-28 19:12:14 | 5 | peach | 9 | 1 |
SQL Fiddle:
http://sqlfiddle.com/#!9/a9fafc/1
How can both selects be merged to get data from oldest and latest rows? Preferably leaving out oldest and latest rows that are the same (like id 5, "peach")
Desired output:
| time | id | titulo | precio | vendidos | oldest_time | oldest_precio | oldest_vendidos |
|---------------------|----|----------|--------|----------|---------------------|---------------|-----------------|
| 2019-10-30 19:12:14 | 1 | apple_b | 3 | 17 | 2019-10-26 19:12:14 | 2 | 10 |
| 2019-10-30 19:12:14 | 2 | pea | 3 | 11 | 2019-10-26 19:12:14 | 3 | 7 |
| 2019-10-29 19:12:14 | 3 | orange_b | 2 | 19 | 2019-10-26 19:12:14 | 1 | 4 |
| 2019-10-29 19:12:14 | 4 | banana | 6 | 14 | 2019-10-28 19:12:14 | 5 | 7 |
I don´t get how can this be done. I tried some things that ended with incorrect results. So does anyone here know how to do this?
Would this be a solution for you:
select * from (
SELECT b.*
FROM (SELECT t.id, MAX(time) AS latest
FROM srapedpubs t GROUP BY id) a
INNER JOIN srapedpubs b ON b.id = a.id AND b.time = a.latest
ORDER BY id ASC) one_t
left join
(SELECT b.*
FROM (SELECT t.id, MIN(time) AS oldest
FROM srapedpubs t GROUP BY id) a
INNER JOIN srapedpubs b ON b.id = a.id AND b.time = a.oldest
ORDER BY id ASC) two_t
on one_t.id = two_t.id
where one_t.vendidos <> two_t.vendidos
And so the result is the same as in Your question:
select one_t.time
, one_t.id
, one_t.titulo
, one_t.precio
, one_t.vendidos
, two_t.time as oldest_time
, two_t.precio as oldest_precio
, two_t.vendidos as oldest_vendidos from (
SELECT b.*
FROM (SELECT t.id, MAX(time) AS latest
FROM srapedpubs t GROUP BY id) a
INNER JOIN srapedpubs b ON b.id = a.id AND b.time = a.latest
ORDER BY id ASC) one_t
left join
(SELECT b.*
FROM (SELECT t.id, MIN(time) AS oldest
FROM srapedpubs t GROUP BY id) a
INNER JOIN srapedpubs b ON b.id = a.id AND b.time = a.oldest
ORDER BY id ASC) two_t
on one_t.id = two_t.id
where one_t.vendidos <> two_t.vendidos