CODEWITHSUNDEEP
mysqlsqlgroup-by
kewl
kewl

Reputation: 54

group by count by min length

my sql query looks like: 

select CategoryID, Name, count(Name) as C 
from Posts 
where CategoryID=1101 and Language=1 
group by CategoryID 
order by C ASC;

I need to get the shortest Name in the Category which is grouped by Name, any ideas?

Another thing would be great if no CategoryID is given (NULL) the group by wouldn't include.

Upvotes: 0

Views: 230

Answers (2)

fthiella
fthiella

Reputation: 49079

This will do the trick:

SELECT
  CategoryID,
  SUBSTRING_INDEX(GROUP_CONCAT(Name ORDER BY LENGTH(Name)), ',', 1) As s_name,
  Count(Name) as C,
FROM
  Posts 
WHERE
  CategoryID=1101 and Language=1 
GROUP BY CategoryID 
ORDER BY C ASC;

Upvotes: 1

simbabque
simbabque

Reputation: 54333

You can do that by ordering by length(Name) and limiting to 1.

select CategoryID, Name, count(Name) as C 
from Posts where CategoryID is not null and Language=1 
group by CategoryID order by length(Name) asc limit 1;

Add CategoryID IS NOT NULL to exclude NULL values.

Upvotes: 0

Related Questions