0

I have two tables:

1st Table Name : country
Table data:

------------------
ID | CountryName | 
------------------
1  | India       |
2  | Japan       |
3  | China       |
4  | Canada      |
5  | Denmark     |
6  | Egypt       |
7  | France      |
8  | Germany     |
9  | Iceland     |
10 | Israel      |
11 | Italy       |
12 | Kenya       |
------------------

2nd Table Name : votes
Table data:

---------------------------------
ID | CountryCode | voteByUserId | 
---------------------------------
1  | 1           |  1           |
2  | 1           |  5           |
4  | 2           |  6           |
4  | 3           |  7           |
4  | 2           |  10          |
4  | 1           |  9           |
4  | 1           |  15          |
4  | 2           |  12          |
---------------------------------

Every user will vote for country and all vote data will be stored in votes Table.

Now I want to display Top 3 most voted country. What will be the SQL query?

Here what I want to :
SELECT * FROM country WHERE ??? ORDER BY ???

Thanks

2
Contributors
3
Replies
4
Views
7 Years
Discussion Span
Last Post by pritaeas
0

I think it should be this:

SELECT CountryName, COUNT(*) AS count FROM country c,votes v WHERE c.ID=v.CountryCode GROUP BY v.CountryCode ORDER BY count DESC LIMIT 3

Edited by pritaeas: n/a

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.