GROUP BY problems

[alphacooler]

Here is my query:

SELECT users.username
FROM articles
INNER JOIN users ON ( articles.userID = users.userID )
GROUP BY users.username
ORDER BY articles.dateAdded DESC
LIMIT 15

And it brings up *close* to expected results except that it leaves out certain users. Who should be there. And these users do indeed have entries in the users table so it isn't a matter of the JOIN limiting the results.

Any possible ideas? I am KILLING myself with this.

 

[guelphdad]

show sample data from both of your tables and an example of which rows you think that should be returned with your query that are not being returned.

note that an INNER join will only return rows where there are matches in both tables. If you want unmatched rows you want a LEFT OUTER JOIN.

 

[alphacooler]

Well the fact of the matter is that if I remove the "group by" I get the correct list. So the join isn't the problem.

Thoughts?

 

[r937]

in a GROUP BY query, the ORDER BY clause may reference only expressions in the SELECT

if you want to ORDER BY articles.dateAdded, then you must SELECT this column

of course, that would (appear to) negate what you're doing by returning unique usernames (unique because there will be one row per username after the GROUP BY)

perhaps you could take a moment and explain the results you are trying to achieve?



r937.com | rudy.ca

 

[alphacooler]

I am trying to get a list of users who have posted articles most recently.

 

[r937]

okay, i'm going to assume you want the latest article for each user

SELECT users.username, max(articles.dateAdded) as last_article_date
FROM articles
INNER JOIN users ON ( articles.userID = users.userID )
GROUP BY users.username
ORDER BY last_article_date DESC
LIMIT 15


r937.com | rudy.ca

 

[alphacooler]

Brilliant! Thanks so much r937. It was the MAX() that did it for me.

I am so incredibly grateful!

Have a wonderful holiday!

Warmest,

k

 

[guelphdad]

Always best to tell us what you are trying to do in the original question rather than leaving that important fact out!