3

This query working 4.0 perfectly but not in 2.3 device, If i remove LIMIT clause and sub query than works perfect in 2.3 device

whats problem with LIMIT in 2.3 device ???

Tested same APK in 4 different device 2.3 show only one result not all and 4.0 works perfect

Here is my fiddle

SELECT * FROM (SELECT pk_video_master_id AS _id,    
video_title AS title1,
COUNT(fk_video_master_id) AS sub_title_1,   
SUM(video_detail_length) AS sub_title_2,
-1007 AS module 
FROM tbl_video_master 
LEFT JOIN tbl_video_detail ON fk_video_master_id = pk_video_master_id
WHERE title1 LIKE '%n%' GROUP BY _id LIMIT 3) 

UNION ALL 

SELECT * FROM (SELECT pk_surveys_id AS _id ,
surveys_title AS title1,
surveys_end_date AS sub_title_1,
COUNT(fk_survey_detail_survey_id) AS sub_title_2,
-1006 AS module
FROM tbl_surveys 
LEFT JOIN tbl_survey_detail ON fk_survey_detail_survey_id = pk_surveys_id 
WHERE title1 LIKE '%n%' GROUP BY _id LIMIT 5);

help me ... thanks

MilapTank
  • 9,988
  • 7
  • 38
  • 53
  • And what is wrong on the 2.3 device? – CL. Mar 28 '14 at 07:46
  • that is the question what is wrong on the 2.3 device? strange but fact i had tested twice by removing sub query with LIMIT its work perfect – MilapTank Mar 28 '14 at 07:48
  • What did not work as expected? What is the error? – CL. Mar 28 '14 at 07:50
  • yes if you see fiddle in that result module column have 2 module -1007 and -1006 so in 2.3 its show only -1006 result not -1007 and 4.0 show all result there is no error but result in query not showing in 2.3 device – MilapTank Mar 28 '14 at 07:51
  • 2
    Older Android versions may use older SQLite versions. Check here: http://stackoverflow.com/questions/2421189/version-of-sqlite-used-in-android?answertab=votes#tab-top – Jozua Apr 14 '14 at 12:52
  • @Jozua how can i get which version of sqlite DB ? – MilapTank Apr 14 '14 at 13:03
  • us this to get version of sqlite http://stackoverflow.com/a/3613043/1109425 – nandeesh Apr 16 '14 at 17:01
  • @milaptank how many records do you see on 2.3 device? – CompEng Apr 16 '14 at 18:36

1 Answers1

0

You can try ROWID BETWEEN 1 AND 5 instead of LIMIT

OR

You can select -1006 and then you can select -1007 as well. So you can enforce the LIMIT by java not sql. I mean do not use union select both of records separately as two result set and then java make union results set as you want

CompEng
  • 7,161
  • 16
  • 68
  • 122