Get row where column2 is X and column1 is max of column1

Question

I have a SQLite table like this:

Col1  Col2  Col3
1     ABC   Bill
2     CDE   Fred
3     FGH   Jack
4     CDE   June

I would like to find the row containing a Col2 value of CDE which has the max Col1 value i.e. in this case June. Or, put another way, the most recently added row with a col2 value of CDE, as Col1 is an auto increment column. What is an SQL query string to achieve this? I need this to be efficient as the query will run many iterations in a loop.

Thanks.

Answer 1

SELECT * FROM table WHERE col2='CDE' ORDER BY col1 DESC LIMIT 1

in case if col1 wasn't an increment it would go somewhat like

SELECT *,MAX(col1) AS max_col1 FROM table WHERE col2='CDE' GROUP BY col2 LIMIT 1