Why is a MySQL 'GROUP BY' clause is reducing my recordcount

[seona]

Hi guys,

I thought I understood the 'Group By' thing - I've used it in numerous other places in the site. But now I'm trying to use it on the results of a search query I'm creating and I'm not sure I understand it anymore.

If I run the query without this line, I return (for the term I'm testing with) 9 records. If I run the query with the line, I return 2 records. Where did the rest of the records go?

My query is:

SELECT *
FROM  r_document AS d,r_docByType AS dt
WHERE  d.id = dt.documentID
AND  (docTitle LIKE '%#FORM.keySearch#%'
OR  docContent LIKE '%#FORM.keySearch#%')
GROUP BY dt.docTypeID

Just in case the database setup isn't clear, r_document contains most of the information about the documents (content) of the site. r_docByType matches a document's ID with the ID of a document type, since occasionally a document can be more than one type (such as counting as both an article and a showcase).

Can anyone help me figure out what's going wrong here?

Cheers,

Seona.

[dramstore]

I would image its because using 'group by' is only returning unique rows?

[seona]

Ah, figured it out thanks to your suggestion. What I needed to do was to also put the next piece in the chain into the statement. So it became:

ORDER BY dt.docTypeID,d.docTitle

...and suddenly the rest of my records came back. :)

Cheers,

Seona.