0

I got the following MySQL query which I need to convert to a SQLite query (to use in my android application)

select *
from msgsList
where (least(msg_from, msg_to), greatest(msg_from, msg_to), msg_time)       
in 
(
    select 
       least(msg_from, msg_to) as x, greatest(msg_from, msg_to) as y, 
       max(msg_time) as msg_time
    from msgsList 
    group by x, y
);
CL.
  • 173,858
  • 17
  • 217
  • 259

2 Answers2

1

In SQLite, LEAST()/GREATEST() are just called MIN()/MAX().

SQLite does not support IN with tuples, but you can join the table to the subquery instead:

SELECT msgsList.*
FROM msgsList
JOIN (SELECT MIN(msg_from, msg_to) AS x,
             MAX(msg_from, msg_to) AS y,
             MAX(msg_time) AS msg_time
     FROM msgsList
     GROUP BY x, y) AS m
ON m.x        = MIN(msgsList.msg_from, msgsList.msg_to) AND
   m.y        = MAX(msgsList.msg_from, msgsList.msg_to) AND
   m.msg_time = msgsList.msg_time;

If you have SQLite 3.7.11 or later (which is not available in all Android versions), you can simply use the aggregate MAX() to select the rows to return:

SELECT *,
       MAX(msg_time)
FROM msgsList
GROUP BY MIN(msg_from, msg_to),
         MAX(msg_from, msg_to);
Community
  • 1
  • 1
CL.
  • 173,858
  • 17
  • 217
  • 259
-1

You can use rawQuery(String query) of SQLiteDatabse class to run as it is.

Mifeet
  • 12,949
  • 5
  • 60
  • 108
Sumit Agrawal
  • 49
  • 5
  • SQLite does not have LEAST()/GREATEST() or IN on tuples. – CL. Aug 20 '15 at 07:24
  • I know, but this is a MySQL query, which isn't SQLite right? –  Aug 20 '15 at 07:24
  • Im not sure, but couldnt you just use max instead of greatest and min instead of meast? Of course i mean the multi-argument max function. So something like that: min(msg_from, msg_to) ? – JoshuadV Aug 20 '15 at 07:28
  • @JoshuadV true, I tried, however I also use several SELECT queries inside one, so I assume that would give some problems too? –  Aug 20 '15 at 07:30