Min value from Database in MySQL

问题:

Am trying to find the min value from past 30 days, in my table there is one entry for every day, am using this query

SELECT MIN(low), date, low 
FROM historical_data 
WHERE name = 'bitcoin' 
ORDER BY STR_TO_DATE(date,'%d-%m-%Y') DESC 
LIMIT 7

But this value not returing the correct value. The structure of my table is

Table structure

And table data which is store is like this

Table data style

Now what i need is to get the minimum low value. But my query not working it give me wrong value which even did not exist in table as well.

Updates:

Here is my updated Table Structure. enter image description here

And here is my data in this table which look like this enter image description here

Now if you look at the data, i want to check the name of token omisego and fatch the low value from past 7 days which will be from 2017-12-25 to 2017-12-19 and in this cast the low value is 9.67, but my current query and the query suggested by my some member did not brings the right answer.

Update 2:

http://rextester.com/TDBSV28042

Here it is, basically i have more then 1400 coins and token historical data, which means that there will me more then 1400 entries for same date like 2017-12-25 but having different name, total i have more then 650000 records. so every date have many entries with different names.

回答1:

To get the lowest row per group you could use following

SELECT a.*
FROM historical_data a 
LEFT JOIN historical_data b ON a.name = b.name 
AND a.low > b.low
WHERE b.name IS NULL
AND DATE(a.date) >= '2017-12-19' AND DATE(a.date) <= '2017-12-25' 
AND a.name = 'omisego'

or

SELECT a.*
FROM historical_data a 
JOIN (
    SELECT name,MIN(low) low
    FROM historical_data
    GROUP BY name
) b USING(name,low)
WHERE  DATE(a.date) >= '2017-12-19' AND DATE(a.date) <= '2017-12-25' 
AND a.name = 'omisego'

DEMO

For last 30 day of 7 days or n days you could write above query as

SELECT a.*, DATE(a.`date`)
FROM historical_data2 a 
LEFT JOIN historical_data2 b ON a.name = b.name 
AND DATE(b.`date`) >= CURRENT_DATE() - INTERVAL 30 DAY 
AND DATE(b.`date`) <= CURRENT_DATE()
AND a.low > b.low
WHERE b.name IS NULL
AND a.name = 'omisego'
AND DATE(a.`date`) >= CURRENT_DATE() - INTERVAL 30 DAY
AND DATE(a.`date`) <= CURRENT_DATE()
;

DEMO

But note it may return more than one records where low value is same, to choose 1 row among these you have specify another criteria to on different attribute

回答2:

Consider grouping the same and running the clauses

SELECT name, date, MIN(low)
FROM historical_data 
GROUP BY name
HAVING name = 'bitcoin' 
AND STR_TO_DATE(date, '%M %d,%Y') > DATE_SUB(NOW(), INTERVAL 30 DAY);

Given the structure, the above query should get you your results.

回答3:

// Try this code ..

SELECT MIN(`date`) AS date1,low
        FROM historical_data
 WHERE `date` BETWEEN now() - interval 1 month 
                  AND now() ORDER by low ASC;