CODEWITHSUNDEEP
mysqldatabaseselect
overboard182
overboard182

Reputation: 300

mysql statment for finding word with highest count value

How can I make a select statement that will allow me to get the distinct words with its corresponding count but the count retrieved has to be the largest value.

For context My table has 3 columns: word, count, url

I go through a bunch of urls and count the occurrences of each word on a page. Both the word and url columns make up a composite primary key.

enter image description here

Not that I will count biology twice, as long as it comes from two different links. That's why it is important I only return one occurrence of the word with its highest count. Any input appreciated.

Upvotes: 0

Views: 31

Answers (3)

Abdullah Dibas
Abdullah Dibas

Reputation: 1507

Try this:

SELECT * FROM YourTable 
INNER JOIN (SELECT word, MAX(count) AS maxCount FROM YourTable GROUP BY word) t 
ON t.maxCount = YourTable.count AND t.word = Yourtable.word

Upvotes: 0

Giorgos Altanis
Giorgos Altanis

Reputation: 2760

select [word], max([count])
from test
group by [word]

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13517

You can try below query - 

SELECT WORD, MAX(COUNT), URL
FROM YOUR_TABLE
GROUP BY WORD

Hope this helps.

Upvotes: 0

Related Questions