SQLite SELECT Help, PLease

I have a SQLiteDatabase and need to retrieve all the columns in a record with the Maximum Integer Value in a specified field.

As an example:
Assume there are 10 columns in a table named “mbr_info” and a few column names are “Dept”, “Div”, “Group”, and “Score”.

I need to retrieve the entire record for a specific Dept, Div, and Group that has the Maximum Score value. All columns are String type except for Score.

I can’t seem to figure out how to combine the Max function with the SELECT * and the WHERE statements.

Thanks to all

SELECT col1,col2,col3, …, col9 FROM mbr_info WHERE div = … AND “group” = … AND dept = … GROUP BY div,“group”,dept HAVING MAX(score) = score

Thanks, Octavian, I will try that the very next chance I get.

I think you will need a sub query so that your main query knows what the max score is so something like:

Select * from table where score =
(select max(score) from table)

Octavian & Simon -

Thank you both for the replies. I am currently doing a “brute force” query as follows:

“SELECT * FROM … WHERE … AND … AND … ORDER BY Score COLLATE BINARY DESC”

With the resulting recordset, I just use the first record. It seems to work well and actually gives different (and better) resultys than Octavian’s query.

Simon - I’m quite dense in this regard and do not understand what a sub query is or how to use one.

A sub query is like a nested (or inner) query inside your select statement that is executed before your ‘main’ select. They have to enclosed in parentheses . You need to know what your maximum score is so that your main (or outer) query knows which rows to return:

SO this statement if say the maximum score was 10:

Select * from table where score =
(select max(score) from table)

Would by executed by SQL as

select * from table where score = 10