0

Here's my simplified working query:

select fields, sum(otherFields)
from table1
where table1.field1 in (Select table2.field1 
                        from table2
                        where table1.id=table2.id 
                        order by table2.date desc limit 100)
group by fields

As you can see, I need from table1 only the rows that are in table2 but these ones filtered by last newer 100.

The query takes a bit too long, so I've tried to replace the in with exists

 select fields, sum(otherFields)
    from table1
    where exists (Select table2.field1 
                   from table 2
                   where table1.id=table2.id 
                     and table1.field=table2.fields
                   order by table2.date desc limit 100)
    group by fields

While this would work for queries that don't use limit in my case it does not work properly.

So, how can I properly filter the result from table1 in relation with only a limited number of rows from table2?

Alin
  • 14,809
  • 40
  • 129
  • 218
  • I guess you could work with a join between both tables, it seems more natural to me. I do not know whether this imporves your query time though, did you test this? – Marcel P Feb 22 '17 at 13:57
  • @MarcelP I had the impression that I need a RIGHT jong with the table 2 select, but it seems that RIGHT join is not supported by sqlite – Alin Feb 22 '17 at 13:59
  • Show the database schema (including any indexes). – CL. Feb 22 '17 at 15:24

0 Answers0