mysql join with sub-query

Question

This is my schema:

mysql> describe stocks;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| symbol    | varchar(32) | NO   |     | NULL    |                |
| date      | datetime    | NO   |     | NULL    |                |
| value     | float(10,3) | NO   |     | NULL    |                |
| contracts | int(8)      | NO   |     | NULL    |                |
| open      | float(10,3) | NO   |     | NULL    |                |
| close     | float(10,3) | NO   |     | NULL    |                |
| high      | float(10,3) | NO   |     | NULL    |                |
| low       | float(10,3) | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
9 rows in set (0.03 sec)

I added the column open and low and I want to fill up with the data inside the table.

These values open/close are referenced to each day. (so the relative max/min id of each day should give me the correct value). So my first insight is get the list of date and then left join with the table:

SELECT DISTINCT(DATE(date)) as date FROM stocks

but I'm stuck because I can't get the max/min ID or the the first/last value. Thanks

Rams · Accepted Answer · 2017-04-06 08:05:18Z

1

You will get day wise min and max ids from below query

 SELECT DATE_FORMAT(date, "%d/%m/%Y"),min(id) as min_id,max(id) as max_id  FROM stocks group by DATE_FORMAT(date, "%d/%m/%Y")

But other requirement is not clear.

answered Apr 6, 2017 at 8:05

Rams

2,1591 gold badge15 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

egariM Over a year ago

Thanks for help, this is the first step i need to replace min_id,max_id with the value of 'value' column and then insert into column high,low

egariM · Accepted Answer · 2017-04-06 09:16:06Z

0

Solved!

mysql> UPDATE stocks s JOIN
    -> (SELECT k.date, k.value as v1, y.value as v2 FROM (SELECT x.date, x.min_id, x.max_id, stocks.value FROM (SELECT DATE(date) as date,min(id) as min_id,max(id) as max_id  FROM stocks group by DATE(date)) AS x LEFT JOIN stocks ON x.min_id = stocks.id) AS k LEFT JOIN stocks y ON k.max_id = y.id) sd
    -> ON DATE(s.date)  = sd.date
    -> SET s.open = sd.v1, s.close = sd.v2;
Query OK, 995872 rows affected (1 min 50.38 sec)
Rows matched: 995872  Changed: 995872  Warnings: 0

answered Apr 6, 2017 at 9:16

egariM

1853 silver badges13 bronze badges

Collectives™ on Stack Overflow

mysql join with sub-query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related