0

Additional help anyone.

I'm trying to get the value of per column 1 and 2 based on time which is column 4.

This is my table

enter image description here

This is what I'm trying to achieve as a result.

enter image description here

as you can see the last row is not there anymore because row 1 and last row is the same when it comes to sensor_id and event_type but row 1 (time) is higher than last row(time)

This is my code so far

SELECT `sensor_id`,`event_type`,`value`, time FROM `events` ORDER BY `time` DESC
PFlorentino
  • 25
  • 7
  • Does this answer your question? [Get records with max value for each group of grouped SQL results](https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results) – pygame Apr 28 '21 at 15:59
  • You can check this question: https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results – pygame Apr 28 '21 at 16:00
  • What is the MySQL version you are using? If it is MySQL 8.0, you can use window function to sort it out. – Anh Duc Ng Apr 28 '21 at 16:08

1 Answers1

1

You can use the GROUP BY clause for this purpose:

SELECT `sensor_id`, `event_type`, `value`, max(`time`)
FROM `events`
GROUP BY `sensor_id`, `event_type`, `value`
ORDER BY max(`time`) DESC
Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • It is correct however can we eliminate the last row since there is a similar sensor_id and event_type on that – PFlorentino Apr 28 '21 at 16:09
  • @PFlorentino we can eliminate the last row, but before we do that, it's important to note that the last line does the sorting you applied in your original query. If there is no sorting, then the groups will not necessarily be ordered by time descendingly. – Lajos Arpad Apr 28 '21 at 16:16