2

I have

Voters(name,vote, score,time)

Where name is the primary key and vote the ID of the person whom the voters voted for. 1 person can vote more than once.

I have to find the name of the person who has voted the most number of times. {Maximum Count(vote)} without using max command.

Kapil Khandelwal
  • 15,958
  • 2
  • 45
  • 52
Guybrush
  • 201
  • 1
  • 4

3 Answers3

3

If you can use count(), then try ordering your results by it. In MySQL, your query could look like

SELECT name, COUNT(vote) AS total_votes
FROM Voters
GROUP BY name
ORDER BY total_votes DESC
LIMIT 1

This would return the person who has voted the most together with his vote count.

Mifeet
  • 12,949
  • 5
  • 60
  • 108
1

You can use concept from relational algebra. limit 1 does not give good result when more than one user have the same number of votes.

On PostgreSQL:

with t as (select name, count(*) c from voters group by name)
SELECT t.* from t
except
SELECT t.* from t JOIN t t2 ON t2.c > t.c

Example on SQL Fiddle

And sample data to prove this works:

create table voters (name int,vote int);

insert into voters values (1,1);
insert into voters values (1,1);
insert into voters values (1,1);
insert into voters values (1,1);
insert into voters values (1,1);
insert into voters values (1,1);
insert into voters values (1,1);
insert into voters values (1,1);
insert into voters values (2,1);
insert into voters values (2,1);
insert into voters values (2,1);
insert into voters values (2,1);
insert into voters values (2,1);
insert into voters values (2,1);
insert into voters values (2,1);
insert into voters values (2,1);
insert into voters values (2,1);
insert into voters values (3,1);
insert into voters values (3,1);
insert into voters values (3,1);
insert into voters values (3,1);
insert into voters values (3,1);
insert into voters values (3,1);
insert into voters values (3,1);
insert into voters values (3,1);
insert into voters values (3,1);
insert into voters values (4,1);
insert into voters values (4,1);
insert into voters values (4,1);
insert into voters values (4,1);
insert into voters values (4,1);
insert into voters values (5,1);
insert into voters values (5,1);
insert into voters values (5,1);
insert into voters values (5,1);

My solution is based on answer to this question: How can I find MAX with relational algebra?

There is also good explanation of this concept in another answer here Aggregate Relational Algebra (Maximum)

Community
  • 1
  • 1
Tomas Greif
  • 21,685
  • 23
  • 106
  • 155
0

Try Following

select count(*) from table where vote=(select max(count(*)) from table) group by vote

Hope this helps you.

Freelancer
  • 9,008
  • 7
  • 42
  • 81