The Problem
we have 2 tables
- user Table
- vote Count Table
as given below:
name
| id | name |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
votCount
| id | vote |
|---|---|
| 1 | 3 |
| 2 | 4 |
| 3 | 7 |
Write a query such that it will show only the name of the user that has the top vote and it has to be in one query without the sub query. First let’s see how it is done with sub queries.
SELECT name.name
FROM name
INNER JOIN votCount
ON votCount.n_id = name.id
WHERE votCount.vote = (SELECT MAX(votCount.vote) FROM votCount)Now let’s see how we can do it in one query and which will take less compute time.
SELECT name.name
FROM name
INNER JOIN votCount
ON votCount.n_id = name.id
ORDER BY votCount.vote DESC
LIMIT 1;Turns out the query was a lot simpler than I thought. But it took me over half an hour to figure it out. Which is probably a bit embarrassing. Since this was asked in an interview question where you should have got it within few minutes.