Min value from Database in MySQL

Question

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.

your post looks to be a repost of your previous question https://stackoverflow.com/questions/47966447/calculate-volatility-ratio-of-historical-data-of-cryptocurrency-market-capitaliz — Funk Forty Niner, Dec 25 '17 at 14:30
@FunkFortyNiner not they are totally different, in previous question i calculate volatility ratio which is different thing, and here am getting low value, there are totally different direction. how you say they are same am shook — Sara Tariq, Dec 25 '17 at 14:34
@FunkFortyNiner check the formula which is use to calculate the volatility ration. — Sara Tariq, Dec 25 '17 at 14:35
Please add your expected result set, Moreover you have used an aggregate function `min()` with out `group by` clause this will return you single row considering all records as a single group, So limit 7 is of not use Please update your question with exactly what values/data you want — M Khalid Junaid, Dec 25 '17 at 14:56

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 26 '17 at 08:54

M Khalid Junaid

63,861
10
90
118

@M Khalid Junaid i find a loop whole. as i have multiple entries for same date but with different names, then it not showing me any result. – Sara Tariq Dec 26 '17 at 09:31
@SaraTariq can you add sample data here http://rextester.com and add your expected data also in your question , From above comment its not clear what is the exact issue – M Khalid Junaid Dec 26 '17 at 09:33
@SaraTariq i have removed data filter have a look at http://rextester.com/BHAPH90206 does this works for you ? – M Khalid Junaid Dec 26 '17 at 09:52
@M Khalid Junaid, i need that filter as well. so that i can check lowest values in past 7 days, 30days or what every range i select. – Sara Tariq Dec 26 '17 at 10:25
@SaraTariq See the update answer and another issue for same low value for morethan 1 rows which row should be returned – M Khalid Junaid Dec 26 '17 at 10:46

Valerian Pereira · Answer 2 · 2017-12-25T15:27:19.233

0

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.

edited Dec 25 '17 at 15:27

answered Dec 25 '17 at 14:23

Valerian Pereira

725
1
6
16

it get the low value but not from last 30 days. it give overall low value – Sara Tariq Dec 25 '17 at 14:30
this did not return any thing. – Sara Tariq Dec 25 '17 at 14:40
Issue! Date is a VARCHAR – RiggsFolly Dec 25 '17 at 15:14
Correct @RiggsFolly. The data for date field should be of type "datetime" to avoid additional overhead. – Valerian Pereira Dec 25 '17 at 15:27
@SaraTariq did the updated query work for you? It will give you the result based on the last 30 days records. – Valerian Pereira Dec 26 '17 at 19:14
@ValerianPereira Thanks for your help i found the solution. – Sara Tariq Dec 26 '17 at 19:22

score 0 · Answer 3 · answered Dec 26 '17 at 08:45

0

// 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;

answered Dec 26 '17 at 08:45

Nimesh Patel

796
1
7
23

Thanks. it show me right date which have lowest value, but the value is not correct it picking the wrong value. means value did not match with date that are showing – Sara Tariq Dec 26 '17 at 08:54
Please change data format in query .. date is not compare because your date format is change .... now() function is fetch default date structure ... – Nimesh Patel Dec 26 '17 at 09:00
i have change the format, you can check it in update section, now date format is datetime – Sara Tariq Dec 26 '17 at 09:04
i already done it, check my update question, i have new images uploaded. – Sara Tariq Dec 26 '17 at 09:31

Min value from Database in MySQL

3 Answers3

Linked