Select a name in sql

Question

I selecting the category name from a category table using the below query

select Category.Name 
  from Product 
    inner  join ProductCategory on ProductCategory.PID=Product.PID 
    inner join ProductMaterial on ProductMaterial.PID=Product.PID 
    left join Category on Category.NodeId=ProductCategory.CID 
  where PID in('2233','4432','5665','1252')  
  group by ProductCategory.CID, ProductMaterial.MID,Category.DanishName

the query is working the result of this query is

Electronics
Electronics
Electronics
Home and Garden

I want only select most number of category name, here I need only Electronics.How to get this.Thanks in advance for help...>>

Answer 1

Try this:

MySQL

SELECT A.name, COUNT(A.name) nameCnt
FROM (SELECT C.Name 
      FROM Product P 
      INNER JOIN ProductCategory PC ON PC.PID=P.PID 
      INNER JOIN ProductMaterial PM ON PM.PID=P.PID 
      INNER JOIN Category C ON C.NodeId=PC.CID 
      WHERE PID IN('2233','4432','5665','1252')  
      GROUP BY PC.CID, PM.MID, C.DanishName 
    ) AS A 
GROUP BY A.name 
ORDER BY nameCnt DESC LIMIT 1;

SQL Server

SELECT TOP 1 A.name, COUNT(A.name) nameCnt
FROM (SELECT C.Name 
      FROM Product P 
      INNER JOIN ProductCategory PC ON PC.PID=P.PID 
      INNER JOIN ProductMaterial PM ON PM.PID=P.PID 
      INNER JOIN Category C ON C.NodeId=PC.CID 
      WHERE PID IN('2233','4432','5665','1252')  
      GROUP BY PC.CID, PM.MID, C.DanishName 
    ) AS A 
GROUP BY A.name 
ORDER BY nameCnt DESC;