CODEWITHSUNDEEP
sqlite
Bunsunburner
Bunsunburner

Reputation: 13

Selecting multiple maximum values? In Sqlite?

Super new to SQLite but I thought it can't hurt to ask.

I have something like the following table (Not allowed to post images yet) pulling data from multiple tables to calculate the TotalScore:

Name       TotalScore
Course1       15
Course1       12
Course2        9
Course2       10

How the heck do I SELECT only the max value for each course? I've managed use

ORDER BY TotalScore LIMIT 2

But I may end up with multiple Courses in my final product, so LIMIT 2 etc won't really help me.

Thoughts? Happy to put up the rest of my query if it helps?

Upvotes: 1

Views: 2675

Answers (2)

Imanuel
Imanuel

Reputation: 3667

You can GROUP the resultset by Name and then use the aggregate function MAX():

SELECT Name, max(TotalScore)
FROM my_table
GROUP BY Name

You will get one row for each distinct course, with the name in column 1 and the maximum TotalScore for this course in column 2.

Further hints

You can only SELECT columns that are either grouped by (Name) or wrapped in aggregate functions (max(TotalScore)). If you need another column (e.g. Description) in the resultset, you can group by more than one column:

...
GROUP BY Name, Description

To filter the resulting rows further, you need to use HAVING instead of WHERE:

SELECT Name, max(TotalScore)
FROM my_table
-- WHERE clause would be here
GROUP BY Name
HAVING max(TotalScore) > 5

WHERE filters the raw table rows, HAVING filters the resulting grouped rows.

Upvotes: 7

Schwern
Schwern

Reputation: 164739

Functions like max and sum are "aggregate functions" meaning they aggregate multiple rows together. Normally they aggregate them into one value, like max(totalscore) but you can aggregate them into multiple values with group by. group by says how to group the rows together into aggregates.

select name, max(totalscore)
from scores
group by name;

This groups all the columns together with the same name and then does a max(totalscore) for each name.

sqlite> select name, max(totalscore) from scores group by name;
Course1|15
Course2|12

Upvotes: 1

Related Questions