[sql] How to get the newest Threads from different forums

[FloV]

Hey everybody,

i'm looking for a way (just the SQL query would be more then enough) to get the 5 recent threads from 10 different forums. Is there a way with just 1 query or do i have to fire 10 queries (one for every forum)?

Thanks in advance for your help.

Florian

 

[Jeff Berry]

I can't really think of a way to do what you're asking for with one simple query. Reason being is the sort you are asking for. MySQL doesn't have the ability to GROUP/SORT/LIMIT the way you want. The only way I can think of doing that is by using the UNION command. This is not much more efficient than running 5 different queries, except for the fact it only requires one connection to the SQL server.

(SELECT * FROM xf_thread WHERE node_id = '1' AND discussion_state = 'visible' ORDER BY post_date DESC LIMIT 5)
UNION
(SELECT * FROM xf_thread WHERE node_id = '2' AND discussion_state = 'visible' ORDER BY post_date DESC LIMIT 5)
UNION
(SELECT * FROM xf_thread WHERE node_id = '3' AND discussion_state = 'visible' ORDER BY post_date DESC LIMIT 5)
UNION 
(SELECT * FROM xf_thread WHERE node_id = '4' AND discussion_state = 'visible' ORDER BY post_date DESC LIMIT 5)
UNION
(SELECT * FROM xf_thread WHERE node_id = '5' AND discussion_state = 'visible' ORDER BY post_date DESC LIMIT 5)

You need to change the node_id values to match the forums you want, or change them to parameters (?) or $variables.

 

[Mouth]

SELECT * FROM xf_thread WHERE node_id IN (1,3,25,32,87...) AND discussion_state = 'visible' ORDER BY post_date DESC LIMIT 5

 

[FloV]

I guess the answer from @Jeff Berry is right.

@Mouth , your query will only give the 5 latest threads, right? But i need the 5 latest threads from multiple forums.

 

[Mouth]

But i need the 5 latest threads from multiple forums.

That's what the "IN (1,3,25,32,87...)" in the SQL is for. Each of the numbers representing the node/forum number that you want included.
Both Jeff and mine will achieve the same result, just one will be faster and use less SQL resources

 

[Jeff Berry]

That's what the "IN (1,3,25,32,87...)" in the SQL is for. Each of the numbers representing the node/forum number that you want included.
Both Jeff and mine will achieve the same result, just one will be faster and use less SQL resources

This is incorrect. You query will only return a total of 5 threads - the 5 most recent threads from all forums combined. This is not what the poster asked for.

My query will return the 5 most recent threads from each of the different forums independently, resulting in 50 threads (25 right now, 50 if he does indeed use 10 forums).

These two queries do not return the same result. Your query is indeed faster and more efficient however, but that point is null considering it does not answer his question. What he is asking for cannot (simply) be done with one SQL statement.

 

[Mouth]

You query will only return a total of 5 threads - the 5 most recent threads from all forums combined. This is not what the poster asked for.

It was indeed what he asked for:- "get the 5 recent threads from 10 different forums"
What he meant to say, and should have said, with your understanding is: "get the 5 recent threads from [each of] 10 different forums"

 

[Jeff Berry]

It was indeed what he asked for:- "get the 5 recent threads from 10 different forums"
What he meant to say, and should have said, with your understanding is: "get the 5 recent threads from [each of] 10 different forums"

There is no reason to argue. I understood his question perfectly.