CODEWITHSUNDEEP
mysqlsqldatabaseselecttop-n
ihjn
ihjn

Reputation: 45

Select row with maximum value from table

I have a table with non-unique column auth_id. I need to select the auth_id value with maximum number of entries.

SELECT auth_id, cnt
FROM (SELECT auth_id, COUNT(auth_id) AS cnt 
FROM articles_authors 
GROUP BY auth_id) articles_num
WHERE cnt = (SELECT MAX(articles_num.cnt))

Here's the data example:

auth_id    article_id
1              2
1              1
1              3
2              2
3              1
3              2

And the output:

auth_id    cnt
1           3

But SQL doesn't see the alias table articles_num. How do I make this WHERE clause with this alias?

Upvotes: 1

Views: 87

Answers (4)

SIDU
SIDU

Reputation: 2278

If I understand correctly, you actually want to get one row of the max of the count:

SELECT auth_id, count(auth_id) as cnt
FROM articles_authors
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1

If more than one auth_id have same max count, we need to update this SQL

Upvotes: 1

Rohit Gaikwad
Rohit Gaikwad

Reputation: 815

Try this.......

select auth_id, count(auth_id) as cnt 
from articles_authors 
group by auth_id 
order by cnt DESC 
limit 1;

Let me know if it resolves your issue

Upvotes: 0

StephaneM
StephaneM

Reputation: 4899

Order your data in descending order in your inner query then just take the first one:

SELECT auth_id, cnt
FROM (
    SELECT auth_id, COUNT(auth_id) AS cnt 
    FROM articles_authors 
    GROUP BY auth_id 
    ORDER BY cnt DESC
     )
LIMIT 1

Upvotes: 1

Mureinik
Mureinik

Reputation: 311308

Using a limit clause would be much simpler - you simply order a query according to some field, and then just take the first row:

SELECT   auth_id, COUNT(auth_id) AS cnt 
FROM     articles_authors 
GROUP BY auth_id
ORDER BY 2 DESC
LIMIT    1

Upvotes: 2

Related Questions