So what I'm actually trying to achieve is filter the most voted on stories. The stories are saved in one table, and all of the votes in another one.
I have this statement to get the vid column order proper:
SELECT vid, COUNT(id) AS votecnt
FROM votes
WHERE status = 1
GROUP BY vid
ORDER BY votecnt DESC
And then I have to filter the stories themselves, and I want the to be ordered just as they are in the first clause.
SELECT vid, body, timestamp
FROM stories
WHERE lv = 1
AND status = 1
The thing is, it's the the order that I need, because the first statement does not return all of the rows that the second one does. It would be even better, if the first query would sort the table by status = 1 count at first, and then total votes.
Any ideas how to join all this into an SQL query? Or should I use PHP in between?