CODEWITHSUNDEEP
androidsqlite
user3448282
user3448282

Reputation: 2699

Getting list of rows with id, grouped by other id

I'm using SQLite in Android (minSdkVersion: 15). I have table like this: Table

I'm trying to get list of last inserted rows with trainingId as a parameter. For example, I'd like to get list of last inserted rows with trainingId = 1 and the result should be:

13---5---1 14---5---1 15---5---1

or if trainingId = 2, then:

10---4---2 11---4---2 12---4---2

I tried something like this:

SELECT * FROM table_name JOIN (SELECT MAX(trainingDoneId) AS trainingDoneId FROM table_name GROUP BY treningId) USING (trainingDoneId)

and 

SELECT * FROM table_name WHERE trainingDoneId IN (SELECT MAX(trainingDoneId) FROM table_name GROUP BY 1)

but in both cases I get this error:

android.database.sqlite.SQLiteException: aggregate functions are not allowed in the GROUP BY clause (code 1)

Any suggestions?

Upvotes: 0

Views: 46

Answers (2)

Vickyexpert
Vickyexpert

Reputation: 3167

Please fire below query it will help you,

  SELECT * FROM table_name where trainingId = 1 AND trainingDoneId in (select max(trainingDoneId) from table_name where trainingId = 1)

Upvotes: 2

Luca Nicoletti
Luca Nicoletti

Reputation: 2407

SELECT id, MAX(trainingDoneId), trainingId FROM table_name WHERE trainingId = 1 ORDERBY id

Try with this solution

Upvotes: 0

Related Questions