CODEWITHSUNDEEP
mysqlgreatest-n-per-group
user9274417
user9274417

Reputation: 43

MySQL select with max in multiple columns

I have a table with stock prices by date, and I'd like to select the values closest to multiple dates. For example, give me the closest price to today, one month ago, and 2 months ago. I can do it in separate statements, is it possible to do in a single statement?

In the below, I'd like to return rows 1, 2, and 6 as the closest to 2/7, 1/7, an d 12/7 dates (best to use abs() to do so?).

+---+------------+---------+----------------+----------------+----------------+
| d | quotedate  |  price  | 2018-02-07diff | 2018-01-07diff | 2017-12-07diff |
+---+------------+---------+----------------+----------------+----------------+
| 1 | 2018-02-06 |  13.796 |             -1 |             30 |             61 |
| 2 | 2018-01-09 | 14.1135 |            -29 |              2 |             33 |
| 3 | 2018-01-02 |  13.822 |            -36 |             -5 |             26 |
| 4 | 2017-12-27 | 13.7365 |            -42 |            -11 |             20 |
| 5 | 2017-12-22 |   13.75 |            -47 |            -16 |             15 |
| 6 | 2017-12-04 |  13.589 |            -65 |            -34 |             -3 |
| 7 | 2017-11-28 |  13.477 |            -71 |            -40 |             -9 |
| 8 | 2017-10-31 |  13.214 |            -99 |            -68 |            -37 |
+---+------------+---------+----------------+----------------+----------------+

I've gotten it to do 1 row, but would love to be able to do all 3 rows at once? Fiddle: Here

Upvotes: 3

Views: 214

Answers (1)

Khan M
Khan M

Reputation: 415

select p1.id, p1.name,
  DATE_FORMAT(p1.quote_date, "%Y-%m-%d") as quotedate,
  p1.price,
  DATEDIFF( p1.quote_date, '2018-02-07' ) as 'diff1',
  DATEDIFF( p1.quote_date, '2018-01-07' ) as 'diff2',
  DATEDIFF( p1.quote_date, '2017-12-07' ) as 'diff3'
from prices p1
left outer join (select id, name, quote_date, price, 
                 DATEDIFF( quote_date, '2018-02-07' ) as 'diff1p2'
                 FROM prices) p2
on (p1.name = p2.name
   AND DATEDIFF( p1.quote_date, '2018-02-07' )< diff1p2
    AND DATEDIFF( p1.quote_date, '2018-02-07' ) <=0


   )
WHERE p1.name = 'Stock1'
AND p2.id is null
union
    select p1.id, p1.name,
  DATE_FORMAT(p1.quote_date, "%Y-%m-%d") as quotedate,
  p1.price,
  DATEDIFF( p1.quote_date, '2018-02-07' ) as 'diff1',
  DATEDIFF( p1.quote_date, '2018-01-07' ) as 'diff2',
  DATEDIFF( p1.quote_date, '2017-12-07' ) as 'diff3'
from prices p1
left outer join (select id, name, quote_date, price, 
                 DATEDIFF( quote_date, '2018-01-07' ) as 'diff1p2'
                 FROM prices) p2
on (p1.name = p2.name
   AND DATEDIFF( p1.quote_date, '2018-01-07' )< diff1p2
    AND DATEDIFF( p1.quote_date, '2018-01-07' ) <=0

You should use union between your 3 queries in order to obtain all 3 rows at once

Upvotes: 1

Related Questions