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

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

Thanks.
It works.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.