CODEWITHSUNDEEP
mysqlsql
John Cogan
John Cogan

Reputation: 1060

Selecting order by matches in second table

I am having a little trouble trying select ids of items ordered by the number of times they have been voted upon. The votes are held in a seperate table but the order is not coming out in the right order.

Could anyone point out where this is going wrong please?

Table CompetitionEntries (Not used in query but maybe relevant)

id | PhotoTitle
=======================
23 |   'Photo 1'
24 |   'Photo 2'
25 |   'Photo 3'
26 |   'Photo 4'
=======================

Table CompetitionVotes

id | IdOfPhotoVotedOn
=======================
10 |   23
11 |   24
12 |   22
13 |   22
14 |   22
15 |   24
=======================

I am trying to use the statement shown below"

SELECT `IdOfPhotoVotedOn`, COUNT(`IdOfPhotoVotedOn`) 'Votes' 
FROM `CompetitionVotes` 
WHERE 1
GROUP BY `IdOfPhotoVotedOn`
ORDER BY 'Votes' DESC, `IdOfPhotoVotedOn`

What I am getting is

IdOfPhotoVotedOn | Votes
==========================
   22            |   3
   23            |   1
   24            |   2

I am trying to get is

IdOfPhotoVotedOn | Votes
==========================
   22            |   3
   24            |   2
   23            |   1

Upvotes: 2

Views: 63

Answers (2)

MatBailie
MatBailie

Reputation: 86775

Try this?

SELECT `IdOfPhotoVotedOn`, COUNT(`IdOfPhotoVotedOn`) 'Votes' 
FROM `CompetitionVotes` 
WHERE 1
GROUP BY `IdOfPhotoVotedOn`
ORDER BY COUNT(`IdOfPhotoVotedOn`) DESC, `IdOfPhotoVotedOn`

Upvotes: 2

Teja
Teja

Reputation: 13544

SELECT * FROM 
(SELECT `IdOfPhotoVotedOn`, COUNT(`IdOfPhotoVotedOn`) 'Votes' 
FROM `CompetitionVotes` 
WHERE 1
GROUP BY `IdOfPhotoVotedOn`) A
ORDER BY A.Votes DESC;

Upvotes: 2

Related Questions