CODEWITHSUNDEEP
javascriptsqlite
Moussawi7
Moussawi7

Reputation: 13279

"Union" with "order by" and "limit" in sqlite not working

I am developing a mobile application and store data for offline uses into a local database using SQLITE. In Order to synchronize my local database with the remote database, I am querying the last 10 favorites, and the last 10 match. using the following query:

select user_id from users where is_favorite>0 order by is_favorite desc limit 10
 union
select user_id from users where is_auto_match>0 order by is_match desc limit 10

note that the below query is working fine,which confirm that there are no error in my local database:

select user_id from users where is_favorite>0
     union
select user_id from users where is_auto_match>0

Upvotes: 0

Views: 836

Answers (2)

Karan Dua
Karan Dua

Reputation: 2411

Use

Select * From(select user_id from users where is_favorite>0 order by is_favorite desc limit 10)
UNION ALL
Select * From(select user_id from users where is_auto_match>0 order by is_match desc limit 10)

Note the use of UNION ALL: UNION ALL gives result with duplicate user_id while UNION removes duplicate user_id from the result

Upvotes: 0

CL.
CL.

Reputation: 180210

ORDER BY is not allowed inside compound queries; the only place where it is allowed is at the end of the entire query, where it affects all records.

To be able to use ORDER BY for the two individial queries, you have to to use subqueries:

SELECT *
FROM (SELECT user_id
      FROM users
      WHERE is_favorite > 0
      ORDER BY is_favorite DESC
      LIMIT 10)
UNION ALL
SELECT *
FROM (SELECT user_id
      FROM users
      WHERE is_auto_match > 0
      ORDER BY is_match DESC
      LIMIT 10)

Upvotes: 1

Related Questions