CODEWITHSUNDEEP
sql
Guybrush
Guybrush

Reputation: 201

Finding max value from count without using max

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.

Upvotes: 2

Views: 442

Answers (3)

Tomas Greif
Tomas Greif

Reputation: 22623

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)

Upvotes: 1

Mifeet
Mifeet

Reputation: 13608

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.

Upvotes: 3

Freelancer
Freelancer

Reputation: 9074

Try Following

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

Hope this helps you.

Upvotes: 0

Related Questions