Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

Unless I'm grossly misunderstanding your structure, a simple join should work fine:

SELECT stories.vid, body, timestamp, COUNT(votes.id) votecnt FROM stories
INNER JOIN votes ON stories.vid=votes.vid
WHERE stories.lv=1 AND stories.status=1
GROUP BY votes.vid
ORDER BY votecnt DESC

I'm not sure what you mean by

It would be even better, if the first query would sort the table by status = 1 count at first, and then total votes.

since your where statement only uses votes/stories that have status=1.

share|improve this answer
    
This works great, for the given parameters. The problem is that there's two statuses for votes - 0 and 1. What I would like is for the given results to be sorted first by maximum votes of status 1, and if the maximum votes of status 1 equals to another records' max votes, then it would be sorted by the total number of votes - of status 0 and 1, that is. Another thing - this query doesn't return the records from the stories table if there is no record for it in the votes table. I don't want that. –  donkapone Apr 12 '11 at 14:25
    
I received all of the needed stories by switching to LEFT JOIN and grouping by stories.vid instead of votes.vid. Now the hardest bit - how do I sort the records properly? –  donkapone Apr 12 '11 at 14:40
    
Glad you figured out the LEFT JOIN bit. As far as your sorting..assuming the stories.status and votes.status are the same (duplicate content for whatever reason), the query is excluding any rows that do not have a status=1, so rows with status=0 won't be counted. Not really an answer, I'm afraid. –  Derek Downey Apr 12 '11 at 16:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.