Can I merge data from latest and oldest rows grouped by?

Question

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?

NorthernDev · Answer 1 · 2019-11-03T16:43:26.610

0

You're almost there. Right now, you have 2 queries returning the information needed. You just need to join them together.

SELECT a.latest as time,a.id,a.titulo,a.precio,a.vendidos,b.oldest as oldest_time, b.precio as oldest_precio, b.vendidos as oldest_vendidos
FROM (
    SELECT id, MAX(time) AS latest, title, precious, vendidos
    FROM srapedpubs t 
    GROUP BY id
) a
INNER JOIN (
    SELECT id, MAX(time) AS oldest, title, precious, vendidos
    FROM srapedpubs
    GROUP BY id
) b
ON b.id=a.id
WHERE b.oldest <> a.latest
ORDER BY id ASC;

edited Nov 03 '19 at 16:43

answered Nov 03 '19 at 16:27

NorthernDev

339
1
6

Thanks for your fast reply. With the union i get data in different rows. And I need it in the same one like in the desired output in the question. – Diego F. Nov 03 '19 at 16:53
I've updated my answer to use a JOIN - UNION was not the right solution and I posted the answer before seeing the requirement of records on the same line – NorthernDev Nov 04 '19 at 17:10

VBoka · Accepted Answer · 2019-11-03T16:38:20.160

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

DEMO

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

DEMO

Excellent! You are so fast! Thank you very much! – Diego F. Nov 03 '19 at 16:49 — Diego F., Nov 03 '19 at 16:49

Can I merge data from latest and oldest rows grouped by?

2 Answers2