Android SQLITE - Not Returning Correct Data?

Question

I'm having an issue with a SQL query in Android - it doesn't seem to be returning the correct result, which I think might be due to my query structure possibly.

This is a running app. I have an initial query that works correctly, that gets me the most common out of the last three runs, which is:

Cursor workout = db.rawQuery("SELECT ActivityID, DistanceID FROM Workout WHERE _id IN(Select _id FROM Workout ORDER BY _id DESC LIMIT 3) Group By ActivityID, DistanceID HAVING COUNT(*) > 1", null);

So now I have the most common distance + activity (running/biking ect..) out of the last three runs, so I wanted to run a query that got me the activity + distance combination on the last three with the fasted time, so I have this:

                Cursor fastestWorkout = db.rawQuery
                            ("Select _id, RunTimeSeconds, Distance FROM Workout WHERE ActivityID = " + workout.getInt(0) +
                            " AND DistanceID = " + workout.getInt(1) +
                            " AND RunTimeSeconds IN " +
                            "(Select Min(RunTimeSeconds) From Workout WHERE ActivityID = " + workout.getInt(0) +
                            " AND DistanceID = " + workout.getInt(1) +
                            " ORDER BY _id DESC LIMIT 3)" +
                            " ORDER BY _id DESC LIMIT 3", null);

So the only thing special here is the sub-query that gets me the fastest time, which I imagine might be at fault, however I can't work out why. The cursor has three results in it at the end of this, when it should be one (There are two activities+distances that are the same and one unique - with different RunTimeSeconds, so it should be pulling back that one that is the fastest.

Any help would be appreciated!

Do you know any good way of displaying SQLLite Data in Android, without having to run a loop through the Cursor to get every result? I'll try to do this anyway! — Kevin Pione
– Kevin Pione, Commented Jul 3, 2014 at 8:50

Philippe Banwarth · Accepted Answer · 2014-07-03 08:40:43Z

1

If I understand correctly you want the row with the fastest run time. Instead of using a subquery to find this value, why not just order by this run time and pick the first result :

         Cursor fastestWorkout = db.rawQuery
                        ("Select _id, RunTimeSeconds, Distance FROM Workout WHERE ActivityID = " + workout.getInt(0) +
                        " AND DistanceID = " + workout.getInt(1) +
                        " ORDER BY RunTimeSeconds LIMIT 1", null);

answered Jul 3, 2014 at 8:40

Philippe Banwarth

17.8k2 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kevin Pione Over a year ago

Because I need to order by the ID to get the last three runs - As I'm getting the fastest, most common run within the last three. I guess actually I could make a temp table with the most common runs and then query that in order of runtime to get the quickest - wish there was a way to do it without using a temp table though! Thanks for your answer!

Philippe Banwarth Over a year ago

Ok, I thought the order by were useless, in the subquery because there it has only one result (it is an aggregate without group by) and in the main query because it has only one result too (unless there a several rows with the same min run time value). I don't understand why your query returns 3 rows. Obviously I missed something, sorry

Kevin Pione · Accepted Answer · 2014-07-03 09:14:14Z

0

Thanks for the help! I managed to figure this one out in the end! Bwt's suggestion with the ordering by run-time is what got me there!

I just used a sub-query to select from the last three runs, then I could order by the run-time and limit it to just one result, getting the fastest run!

        Cursor fastestWorkout = db.rawQuery
                       ("Select _id, RunTimeSeconds, DistanceID FROM (Select _id, RunTimeSeconds, DistanceID, ActivityID FROM Workout ORDER BY _id DESC LIMIT 3) WHERE ActivityID = " + workout.getInt(0) +
                        " AND DistanceID = " + workout.getInt(1) +
                        " ORDER BY RunTimeSeconds LIMIT 1", null);

Thanks for your help ans answers!

answered Jul 3, 2014 at 9:14

Kevin Pione

2993 silver badges13 bronze badges

Collectives™ on Stack Overflow

Android SQLITE - Not Returning Correct Data?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related