Sqlite3 commands to extract max value of a column for a specified range of rows?

Question

source: link

May I know what is the correct sqlite3 command to find the max value of column TracKid for let say from rows 4 to 8?

I first tried the following method but was returned None:

def get_rows_max_id(self, index, span=100,):
    sql ="""SELECT MAX(Trackid) FROM table LIMIT (?) OFFSET (?)"""
    self.cur.execute(sql, (span, index,))
    return self.cur.fetchone()

I next tried using a combination of python and sqlite commands and could get the answer.

def get_rows_max_id(self, index, span=100,):
    sql ="""SELECT Trackid FROM table LIMIT (?) OFFSET (?)"""
    self.cur.execute(sql, (span, index,))
    return max(self.cur.fetchall())

However, I believe a pure sqlite3 command would be able to do the job. Can you tell me what is the correct Sqlite commands to use? Pls note that column Trackid can be randomly ordered and may not follow the ascending order that is shown in the picture.

Tables are unordered datasets. There is no such thing as 4th or 8th row of a table. You must define the order of the rows in a query by using the ORDER BY clause. — forpas, Aug 16 '23 at 08:47

score 1 · Accepted Answer · edited Aug 16 '23 at 08:58

Based on the search results and the problem you are trying to solve, it seems you want to find the maximum Trackid value within a specified range of rows in your SQLite database.

The LIMIT and OFFSET keywords in SQLite are useful for specifying the range of rows to consider. However, they do not operate on the result of a MAX function in SQLite. Therefore, you can't directly use these keywords to find the max value within a range of rows.

But, there is a workaround for this. You can use a subquery to first select the range of rows you are interested in, and then find the max value from this subset. Here is how you can modify your function to implement this:

def get_rows_max_id(self, index, span=100):
    sql = """SELECT MAX(Trackid) FROM (SELECT Trackid FROM table LIMIT ? OFFSET ?)"""
    self.cur.execute(sql, (span, index,))
    return self.cur.fetchone()

In this function, the subquery SELECT Trackid FROM table LIMIT ? OFFSET ? first selects a subset of rows from the table based on the provided index and span. Then, the outer query SELECT MAX(Trackid) FROM ... finds the maximum Trackid from this subset.

Sqlite3 commands to extract max value of a column for a specified range of rows?

1 Answers1