CODEWITHSUNDEEP
androidandroid-sqlite
nkoroi
nkoroi

Reputation: 93

room db multiple column subquery

select * from generic_shop 
    where (type, timestamp) in (
        select  type, max(timestamp) 
            from generic_shop 
            group by type
    ) order by type

This query works if tested in the command line but on a room it claims there is an error at where (type, timestamp). How do I rewrite this to get it working? Thanks in advance.

Upvotes: 0

Views: 864

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522626

SQLite does not support IN clauses involving more than one column on the left hand side. But, your query is easy enough to express using other means:

SELECT g1.*
FROM generic_shop g1
INNER JOIN
(
    SELECT type, MAX(timestamp) AS max_timestamp
    FROM generic_shop
    GROUP BY type
) g2
    ON g1.type = g2.type AND g1.timestamp = g2.max_timestamp;
ORDER BY
    g1.type;

Upvotes: 2

Related Questions