CODEWITHSUNDEEP
mysqlsql
theReverseFlick
theReverseFlick

Reputation: 6054

Find a particular column's value that repeats the most in a table

So the Table structure is this

      CD id        |          Artist id

I want to return the artist who has the most CDs, in this case the artist column value that repeats most.

I'm using Mysql so what is the best SQL query to do this?

Upvotes: 0

Views: 138

Answers (2)

Irvin Dominin
Irvin Dominin

Reputation: 30993

I'dont use MySQL I translate a similar TSQL and it becomes like this:

SELECT ArtistId, count(CDId) AS CDCount FROM Table
GROUP BY ArtistId ORDER BY CdCount DESC LIMIT 1

I hope can help you. Btw: I remove column spaces in my example ok?

Upvotes: 1

Phil
Phil

Reputation: 165065

SELECT `Artist id`, COUNT(`CD id`) AS `cd_count`
FROM `Table`
GROUP BY `Artist id`
ORDER BY `cd_count` DESC
LIMIT 1

Upvotes: 6

Related Questions