Danilo Calzetta Danilo Calzetta - 7 months ago 10
SQL Question

Sql select the winner

having this database, bold = PK

CERTIFICATE(USERID, CERTIFICATENAME)

i need to find the userid with the maximum number of certificates with a SQL query.

sample data:

USERID, CERTIFICATENAME
1,cert1
1,cert2
1,cert3
2,cert4
2,cert5
3,cert2
4,cert1


with this sample data i need a query for find that user:1 has 3 certificates, this user has the maximum number of certificates.

request result:

USERID, COUNT
1,3


in this case my dbms is oracle, but i'm looking for a generic sql solution to my problem.

Answer

Using old plain group by:

select top 1 userid, count(certificatename) total
from certificates
group by userid -- but not certificatename
order by 2 desc --you can use total or count(certificatname) here

Common Table Expressions (CTE) don't add any performance preferences because you need group by in any case.

Comments