SQLITE request syntax error

Question

I have an issue with a request, it works fine with pgsql and mysql, but with sqlite, it seems like I am missing something

here is the request :

select * from mesure_insitu 
where (id_formulaire, gid) IN (
  select distinct id_formulaire, max(gid) as gid 
  from mesure_insitu 
  where id_dispo_comp_ouvr = 1 
  GROUP BY id_formulaire 
  ORDER BY id_formulaire ASC
)

This subquery below works fine:

select distinct id_formulaire, max(gid) as gid 
from mesure_insitu 
where id_dispo_comp_ouvr = 1 
GROUP BY id_formulaire 
ORDER BY id_formulaire ASC)

I think the problem is with the condition with two values (id_formulaire, gid), like sqlite cannot use a condition with two values.

I will appreciate any type of help.

Answer 1

To get this query to work, update your SQLite to a newer version.

If you cannot do this, you have to use a join instead:

SELECT *
FROM mesure_insitu
JOIN (SELECT id_formulaire, max(gid) AS gid 
      FROM mesure_insitu 
      WHERE id_dispo_comp_ouvr = 1 
      GROUP BY id_formulaire)
USING (id_formulaire, gid);

(The DISTINCT and ORDER BY are superfluous in this subquery.)