wiZZmnma wiZZmnma - 15 days ago 7
MySQL Question

MySQL Get Min, Max and oldest grouped by

I have a small project I'm working on and have the following setup:

MariaDB [b7_19195200_prices]> select * from watchlist;
+----+-------------+-------+---------------------+--------+
| id | item | price | recorded | errors |
+----+-------------+-------+---------------------+--------+
| 3 | 32725999014 | 1.46 | 2016-11-21 20:30:22 | 0 |
| 4 | 32725999014 | 1.93 | 2016-11-21 20:56:21 | 0 |
| 5 | 32725999014 | 2.01 | 2016-11-21 20:56:32 | 0 |
| 6 | 122444 | 22.43 | 2016-11-21 21:03:19 | 0 |
| 7 | 32725999014 | 1.11 | 2016-11-21 21:25:33 | 0 |
+----+-------------+-------+---------------------+--------+
5 rows in set (0.00 sec)


So I'd like to get the max of price, the min of price and the first (oldest recorded of that item's price). So I'd get a result like:

+----+-------+-------+-------------+----------+
| id | low | high | item | original |
+----+-------+-------+-------------+----------+
| 6 | 22.43 | 22.43 | 122444 | 12.11 |
| 3 | 1.11 | 2.01 | 32725999014 | 0.51 |
+----+-------+-------+-------------+----------+


where low, high and original are the lowest, highest and original prices of the item respectively. A bonus would be to be able to know when it's highest and lowest were. I have been using 2 different queries for getting the highest, lowest and original prices as:

SELECT `id`, MIN(`price`) low, MAX(`price`) high, `item`
FROM `watchlist`
GROUP BY `item`;


and

SELECT `id`, MIN(`recorded`), `price` original, `item`
FROM `watchlist`
GROUP BY item;


anyway I can accomplish that?

Thanks!

Answer

if you don't need the id you can use a join

 select t1.*, t2.* 
 from ( 
  SELECT MIN(`price`) low, MAX(`price`) high, `item`
  FROM `watchlist`
  GROUP BY `item`
) t1
left  join (
  SELECT  MIN(`recorded`), `price` original, `item`
  FROM `watchlist`
  GROUP BY item
) t2 on t1.`item` =  t2.`item`

otherwise if you need also the id join and tuple on subselect

 select t1.*, t2.* 
 from ( 
  SELECT MIN(`price`) low, MAX(`price`) high, `item`
  FROM `watchlist`
  GROUP BY `item`
)  t1
left  join (
  select `id`, `recorded`,  `price`, `item`
  from `watchlist`
  where (  `item`,  `recorded`) in (
        select `item`,  min(`recorded`)
        FROM `watchlist`
        GROUP BY item
  )
) t2 on t1.`item` =  t2.`item`

the second should be correct becase select the rows explicatlly and get the first result for non aggregated valued