MYSQL SELECT - DISTINCT(MAX 1) and ORDER BY

Databases
1

Can someone pls help me with this? Tried everything I could find.

Columns:
id, to, from, message, date, time

I have this set:

‘12’, ‘mike@hotmail.se’, ‘tina@hotmail.com’, ‘bimbam’, ‘2020-07-15’, ‘18:48:59.844557’
‘13’, ‘mike@hotmail.se’, ‘malin@hotmail.com’, ‘mitt meddelande4’, ‘2020-07-15’, ‘20:00:18.323637’
‘26’, ‘mike@hotmail.se’, ‘tina@hotmail.com’, ‘mitt meddelande4’, ‘2020-07-15’, ‘20:21:23.456423’
‘28’, ‘mike@hotmail.se’, ‘malin@hotmail.com’, ‘Hej cookie’, ‘2020-07-17’, ‘23:23:43.444444’

How can I get this from the set above?
(distinct on column: “from” and order by date DESC, time DESC)

‘28’, ‘mike@hotmail.se’, ‘malin@hotmail.com’, ‘Hej cookie’, ‘2020-07-17’, ‘23:23:43.444444’, ‘2’
‘26’, ‘mike@hotmail.se’, ‘tina@hotmail.com’, ‘mitt meddelande4’, ‘2020-07-15’, ‘20:21:23.456423’, ‘1’

2

might be easier with window functions, but not everyone is on a sufficiently recent MySQL version

would definitely be easier if you had used a single DATETIME column instead of separate DATE and TIME columns

SELECT t.id
     , t.to
     , t.from
     , t.message
     , t.date
     , t.time
  FROM ( SELECT x.from
              , MAX( DATE_ADD(CAST(x.date AS DATETIME)     
                     , INTERVAL TIME_TO_SEC(x.time) SECOND) 
                   ) AS latest
           FROM this_set x
         GROUP
             BY x.from ) m
INNER
  JOIN this_set t
    ON t.from = m.from
   AND DATE_ADD(CAST(t.date AS DATETIME)     
                     , INTERVAL TIME_TO_SEC(t.time) SECOND)  
       = m.latest

caution: untested

3

I should try first plain SQL using dbfiddle https://www.db-fiddle.com/f/eaQG8H4yqY9hnQBZjzJgz/1

“SELECT * FROM table etc…”

When the plain SQL query is working, then translate it to whatever your ORM is. PDO?

4

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.