Help with this SQL query

[Red Squirrel]

Say I have a table of topics in a forum, with the following parameters as follows: (obviously there's more but these are the only important ones)

last_update_date: last time it was updated
id_last_msg: The last post that is in this topic
id_board: The forum that it's in.

I want to make a query that will return a list of the last topic of each forum I specify in a IN function. Take this query for example:

SELECT id_board, id_last_msg FROM smf_topics WHERE id_board IN (1,23) group by id_board

But what I want to actually do is make it so the rows returned are the last topic in that forum, so where last_update_date is the largest but only for that particular forum. I was trying to figure out a way using the max function but no go.

How do I do this in a single query? It would be easier to do it in separate queries and just use ORDER BY and LIMIT 1 but that would require revamping pretty much the whole code of what I'm changing.

 

[KLin]

select smf_board.id_board, id_last_msg
from
smf_topics inner join
(select id_Board, max(last_update_date) As LastDate from smf_topics group by id_board) LatestThread on smf_topics.id_board = LatestThread.id_board and smf_topics.last_update_date = LatestThread.LastDate
Where smf_topics.id_board in(1,23)

See if that works.

 

[MrPickins]

Looks like KLin nailed it: join to a subquery that does the aggregation.

 

[Red Squirrel]

Cool thanks that seems to work. It kind of crossed my mind to look into doing a sub query but was not sure if it's good practice or not.

 

[pauldun170]

SELECT
id_board, id_last_msg,
RANK() OVER (PARTITION BY id_board ORDER BY last_update_date DESC) AS lstpost
FROM your_table
WHERE id_board in ('1','2','3') and lstpost= 1

 

[Red Squirrel]

It looks like SMF (the forum software I'm making this change on) does not allow sub queries as it errors with "hacking attempt" but there is a way to fix it by changing a setting value and changing it back. This is my end code:

		$old_disableQueryCheck_value = $modSettings['disableQueryCheck'];		
		$modSettings['disableQueryCheck'] =  1;  //this is required as by default subqueries are not allowed
		
		// Find the latest message on this board (highest id_msg.)
		$request = $smcFunc['db_query']('', '
			SELECT {db_prefix}topics.id_board, id_topic, id_last_msg as id_msg, last_update_time FROM {db_prefix}topics 
			inner join (select id_Board, max({db_prefix}topics.last_update_time) As LastDate
			from {db_prefix}topics group by id_board) LatestThread on {db_prefix}topics.id_board = LatestThread.id_board 
			and {db_prefix}topics.last_update_time = LatestThread.LastDate 
			Where {db_prefix}topics.id_board IN ({array_int:board_list}) AND approved = {int:approved}',
			array(
				'board_list' => $setboards,
				'approved' => 1,
			)
		);
		
		$modSettings['disableQueryCheck']=$old_disableQueryCheck_value;

Mostly just leaving that here for my own future reference if I forget what I did, or if someone else happens to stumble upon this issue.