sq, The only first value for each day

Question

I hava a short question: I need to take a first value for each day in the table like below:

Date                Price
2018-09-01 10:00:00 123
2018-09-01 10:01:00 2568
2018-09-01 10:02:00 2155
2018-09-01 10:03:00 23
2018-09-02 10:03:00 25868
2018-09-02 10:10:00 84213
2018-09-03 15:10:00 58192
2018-09-03 10:16:00 36

So result should be like this:

Date                Price
2018-09-01 10:00:00 123
2018-09-02 10:03:00 25868
2018-09-03 10:16:00 58192

My query looks like this:

SELECT price, min(date)
FROM table
WHERE data BETWEEN '2018-09-01%' AND "2018-09-03%"
GROUP BY date

And I have an issue here. I can't write a query that will take into account first minutes for each day. Could any one help me to solve this issue ?

Thanks for any answers

Zaynul Abadin Tuhin · Answer 1 · 2018-09-18T06:19:14.207

2

use co-related sub-query

select * from t t1
where date in (select min(date) from t t2 
                     where date(t1.date)=date(t2.date)
                      group by date(date)
                  )

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=47d5494c6158ebcfeea7e80d55f3a589

date                   prince
2018-09-01 10:00:00     123
2018-09-02 10:03:00     25868
2018-09-03 10:16:00     36

edited Sep 18 '18 at 06:19

answered Sep 18 '18 at 06:13

Zaynul Abadin Tuhin

31,407
5
33
63

score 0 · Answer 2 · answered Sep 18 '18 at 06:04

0

You need to select the date and the sort by the time and then limit your query to one.

SELECT price FROM table WHERE date = (date object) SORT BY date LIMIT=1

answered Sep 18 '18 at 06:04

MegaMind

653
6
31

1

This will only return 1 row, not the first row for each day. – Barmar Sep 18 '18 at 06:23

score 0 · Answer 3 · answered Sep 18 '18 at 06:06

0

Try integrating 'limit = 1' in your query. This reduces the result to one output

answered Sep 18 '18 at 06:06

mihir

15
5

1

He wants 1 output per day, not 1 output total. – Barmar Sep 18 '18 at 06:23

score 0 · Answer 4 · answered Sep 18 '18 at 06:07

0

try this:

SELECT t.price, t.date
FROM table t
JOIN (SELECT price, min(date) as date
      FROM table 
      GROUP BY price) t2
  ON t2.date = t.date
  AND t2.price = t.price
WHERE t.date BETWEEN '2018-09-01%' AND "2018-09-03%"

answered Sep 18 '18 at 06:07

Justin

9,634
6
35
47

but t2 doesn't exist, at least that's why pgadmin shows – Tmiskiewicz Sep 18 '18 at 06:16
:) it's the same table joined.... table and table, If there not exists such a table your query is wrong – Justin Sep 18 '18 at 06:25

Sandesh Gupta · Answer 5 · 2018-09-18T06:16:09.787

0

Try grouping by Date and then get minimum of both date and price

SELECT price, date from table where date in 
(
  SELECT min(date)
  FROM table
  WHERE date BETWEEN '2018-09-01%' AND "2018-09-03%"
  GROUP BY DATE(date)
) ;

edited Sep 18 '18 at 06:16

answered Sep 18 '18 at 06:08

Sandesh Gupta

1,175
2
11
19

thanks, but I dont need a min price, it could be any price – Tmiskiewicz Sep 18 '18 at 06:11
@Tmiskiewicz Yes, my bad. Updated the answer' – Sandesh Gupta Sep 18 '18 at 06:16

sq, The only first value for each day

5 Answers5