0

I have a temperature table with postcode, temperature and date. How do I get the day with the lowest temperature for a postcode within a dedicated date-period?

table temperatures:

postcode | temperature | date
-----------------------------------
12345      | 35.7          | 2022-02-01  
12345      | 32.8          | 2022-02-02  
12345      | 31.3          | 2022-02-03  
34567      | 35.2          | 2022-02-01  
34567      | 36.9          | 2022-02-02

Min-Temperature output for the date period 2022-02-01 to 2022-02-02 should be:

12345      | 32.8          | 2022-02-02  
34567      | 35.2          | 2022-02-01  

SELECT postcode, MIN(temperature), `date` FROM temperatures
WHERE `date` >= '2022-02-01' and `date` <= '2022-02-02' 
GROUP BY postcode

doesn´t work.

The only idea I had was to use a cursor, but this isn´t the right approach, isn´t it?

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
martin
  • 1
  • 2
  • 2
    ___Doesnt work___ How – RiggsFolly Jun 08 '23 at 17:47
  • Stu -- he wants the lowest number not the most recent record. But my guess would be that a datatype is incorrect, in your database are the dates stored in a date datatype or are they varchar? – easleyfixed Jun 08 '23 at 17:50
  • If it does not work because of "SELECT list is not in GROUP BY clause and contains nonaggregated column 'fiddle.mytable.date' which is not functionally dependent on columns in GROUP BY clause; **this is incompatible with sql_mode=only_full_group_by**" you could have said so (see: https://dbfiddle.uk/4Z9soR91) There are some question/answer about that subject too. – Luuk Jun 08 '23 at 17:59
  • doesn´t work means: Result will be 12345 | 32.8 | 2022-02-01 and 34567 | 35.2 | 2022-02-01 – martin Jun 08 '23 at 18:01
  • No, result is an error, see the dbfiddle I commented 4 minutes ago. LEARN from that error, and your problem will be solved. – Luuk Jun 08 '23 at 18:04
  • @easleyfixed datatypes are correct: tinytext, float, date – martin Jun 08 '23 at 18:10
  • Yeah we are going to need to see the exact part where it doesn't work to figure out what part isn't working. – easleyfixed Jun 08 '23 at 18:12
  • @martin: Maybe this Q/A does give a hint: [Using group by on multiple columns](https://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns) (Because I refuse to help on such basic SQL problems) – Luuk Jun 08 '23 at 18:12
  • Thanks, @Luuk, you´re right. But how can I solve it? – martin Jun 08 '23 at 18:14
  • Take a course in SQL – Luuk Jun 08 '23 at 18:15
  • @easleyfixed Luuk made a dbfiddle that shows the error (see comment above). But I still don´t know how to do it. – martin Jun 08 '23 at 18:17
  • @Luuk Wow! Thanks! – martin Jun 08 '23 at 18:20
  • @martin: Not only you but some other folks (more than 10 year ago), struggled with the problem, see: [SQL query learning stuff](https://stackoverflow.com/questions/4136851/sql-query-learning-stuff) – Luuk Jun 08 '23 at 18:25
  • @Luuk Hey man, just give me the SELECT statement for my problem and everything will be fine. – martin Jun 08 '23 at 18:29
  • @easleyfixed the link in the close-reason answers this and many very similar questions - it doesn't matter if it's the least or greatest or oldest or newest, the same principle applies. – Stu Jun 08 '23 at 19:27

0 Answers0