Why im getting wrong number of rows when using group by and limit?

[gordon57]

Hi everybody, i'm doing a query where i want retrieve some records when i do the query without 'limit' the result returns me the correct number of rows, but when i use limit the result returns me the half of the rows. the query im using is something like this:

SELECT
WGI_TaxoName.ScientificName,		  
WGI_Locality.Locality,
IFNULL(CONCAT(GROUP_CONCAT(IFNULL(CONCAT(wgi_agent.lastname,", ",wgi_agent.firstname),wgi_agent.lastname) SEPARATOR "; "),"; ",GROUP_CONCAT(wgi_agent.name SEPARATOR "; ")),		  IFNULL(GROUP_CONCAT(IFNULL(CONCAT(wgi_agent.lastname,", ",wgi_agent.firstname),wgi_agent.lastname) SEPARATOR "; "),GROUP_CONCAT(wgi_agent.name SEPARATOR "; "))) as Collector
FROM
WGI_CollectionObject
INNER JOIN WGI_CollectingEvent ON (WGI_CollectionObject.CollectingEventID=WGI_CollectingEvent.CollectingEventID)
INNER JOIN WGI_Determination ON (WGI_Determination.CollectionObjectID=WGI_CollectionObject.CollectionObjectId)
INNER JOIN WGI_Locality ON (WGI_CollectingEvent.LocalityID=WGI_Locality.LocalityID)
INNER JOIN WGI_TaxonName ON (WGI_Determination.TaxonNameID=WGI_TaxonName.TaxonNameID)
LEFT OUTER JOIN WGI_Geography ON (WGI_Locality.GeographyID=WGI_Geography.GeographyID)
LEFT OUTER JOIN wgi_collectors ON (wgi_collectors.CollectingEventID=WGI_CollectingEvent.CollectingEventID)
 LEFT OUTER JOIN wgi_agent ON (wgi_collectors.AgentID=wgi_agent.AgentID)
WHERE
(wgi_taxonname.NombreCientifico = "XXX") AND (wgi_taxonname.Genero = "XXX") 
GROUP BY 
WGI_CollectionObject.CollectionObjectId,	  
ORDER BY ScientificNumber
LIMIT 0,10

This query returns me 5 rows and the result should be 10 rows, when i do the query without limit it returns me 10 rows and i dont know why is this happening.

Sometimes the resulting ammount of rows is different i think it depends of the total row count of the query

Thanks for the help.

[djpaul]

Hey Grodon57,
Does the comma need to be there after WGI_CollectionO bject.Collectio nObjectId,?

# GROUP BY 
# WGI_CollectionObject.CollectionObjectId,      
# ORDER BY ScientificNumber
# LIMIT 0,10