0

I have written the following query 

String rawQuery="select * FROM (select email,name,createdat,ROW_NUMBER() OVER (PARTITION BY email ORDER BY createdat DESC) AS RowNumber FROM vitals ) a where RowNumber=?";`

This should return me data that contains unique email id but other data that is not unique. But i am getting the following error

Caused by: android.database.sqlite.SQLiteException: near "(": syntax error (code 1): , while compiling: select * FROM vitals,(select email,name,createdat,ROW_NUMBER() OVER (PARTITION BY email ORDER BY createdat DESC) AS RowNumber FROM vitals ) a where RowNumber=?

i refered to this linkenter link description here

Community
  • 1
  • 1
Shaan_B
  • 1,788
  • 2
  • 12
  • 16
  • Have you tried executing the query? It looks wrong to me. Thats why you are getting syntax error. Can you please provide the schema of tables then I can write a proper query for you :D – Sandeep Bhandari Mar 02 '16 at 07:07
  • this is my table db.execSQL("CREATE TABLE IF NOT EXISTS vitals(name STRING,email STRING,phone STRING,age STRING,gender STRING,height STRING," + "weight STRING,boneMass STRING,muscleMass STRING,bodyFat STRING,visceralFat STRING,bodyWater STRING,bmi STRING,bmr STRING,createdat varchar(2));"); – Shaan_B Mar 02 '16 at 07:16
  • Why cant you just write, select email,name,createdat from vitals where email = 'whatever@gmail.com' order by (createdat); – Sandeep Bhandari Mar 02 '16 at 07:58
  • What is the minimum required Android version for your app? Also, show some example data and the desired result. – CL. Mar 02 '16 at 08:49
  • min required version is 18 this is my db email date name a@gmail.com 3/2/2016 a a@gmail.com 3/2/2016 b a@gmail.com 2/2/2016 a b@gmail.com 3/2/2016 c this is my required output email date name a@gmail.com 3/2/2016 a b@gmail.com 3/2/2016 c sorry i dont know how to add a sample to a comment – Shaan_B Mar 02 '16 at 09:01
  • thanks everyone but the query that i wanted is as follows ' String myQuery="select email,name,createdat from vitals group by email order by date(createdat) desc"; ' – Shaan_B Mar 02 '16 at 11:36

0 Answers0