OK, as is typical, I’ve started off an experimental project and bitten off WAY more than I can chew. (this is my first try at SQL). The problem-
I’ve got two tables, urls and comments:
**tbl urls **| id_urls PRI | url_urls | date_urls | kills_urls
**tbl comments **| id_comments PRI | text_comments | date_comments | votes_comments | id_urls_comments
I’ve used this:
SELECT url_urls, kills_urls, date_urls, text_comments, MAX(votes_comments) FROM (urls LEFT JOIN comments ON urls.id_urls=comments.id_urls_comments) GROUP BY url_urls ORDER BY kills_urls DESC
to return url_urls, kills_urls, date_urls, and text_comments in order by kils_url descending. The problem is that GROUP just picks the first match in the joined second table for text_comments, and I want those ordered by votes_comments. (the MAX() thing obviously doesn’t work.) I’ve dug around looking for a solution to this for a while, and the only thing I can come up with refers to the “groupwise aggregate” problem. All the solutions are frankly making my head swim, particularly with integrating two tables. Anybody got any ideas?