Android SQLite get only one distinct column and get all other columns

Question

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

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 = '[email protected]' 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 [email protected] 3/2/2016 a [email protected] 3/2/2016 b [email protected] 2/2/2016 a [email protected] 3/2/2016 c this is my required output email date name [email protected] 3/2/2016 a [email protected] 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

Android SQLite get only one distinct column and get all other columns

0 Answers0