3

Given an SQLite database with three columns (char, char, int), the following query

select column2, avg(column3) from table_name group by column2; 

will return the average values of column3 aggregate for each distinct value of column2.

What will the following query return?

select column1, column2, avg(column3) from table_name group by column2; 

I came across this case while playing with Codeacademy's SQL tutorial. This particular query returns a value of column1 for each distinct value of column2, but I can't figure out how these values were chosen.

1
  • 3
    Also worth pointing out that in most other engines this sort of potentially non-deterministic query will simply raise an error.
    – Dave Jones
    Sep 24, 2016 at 9:04

1 Answer 1

2

The database will return the value from some arbitrary row in the group. (In the current implementation, it's the value from that last row in the group that was processed, but due to indexes or other optimizations, the processing order might not be predictable.)

Since SQLite 3.7.11, using MIN() or MAX() guarantees that values from a row with the minimum/maximum value are returned.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.