MySQL: group by and IF statement

Question

By default, parent_id = 0. I want to select all records with parent_id = 0 and only the last ones with parent_id > 0.

I tried this, but it didn't work:

SELECT * FROM `articles`
  IF `parent_id` > 0 THEN
GROUP BY `parent_id`
HAVING COUNT(`parent_id`) >= 1
END;
ORDER BY `time` DESC

I mean, that if there are a few records with parent_id = 2, only one of them should be return. Also, if there a number of records with parent_id = 5, only one of them is returned. In other words, there should no more than one record of each parent_id apart from those having parent_id = 0.

What could be the solution?

Answer 1

You're mashing all the concepts together. WHERE, GROUP BY, and HAVING do different things. You probably need to read this page more thoroughly:

http://dev.mysql.com/doc/refman/5.1/en/select.html

But if I make some assumptions about what you're doing...

You probably want to start with this part:

SELECT * FROM articles WHERE parent_id > 0

But now you want to group together the articles that have the same parent. Assuming that each article has an id, perhaps you want

SELECT parent_id, COUNT(*) AS article_count FROM articles WHERE parent_id >= 0 GROUP BY parent_id

Now, all of these rows will be parent_id values with at least one child article, but if you wanted to only look at parents with more than 2 child articles, you might then want:

SELECT parent_id, COUNT(*) AS article_count
FROM articles
WHERE parent_id >= 0
GROUP BY parent_id
HAVING article_count >= 2

That will show you the identity of all parent articles with at least 2 child articles.

Answer 2

If I understood you question well, you need all records with parent_id == 0 and only records with the greatest value of 'time' column for other parent_id's ? I suppose that parent_id + time is unique.

 SELECT a.* FROM `articles` a 
 INNER JOIN 
 (SELECT parent_id, MAX(`time`) AS `time` FROM `articles` WHERE parent_id >0) t
 ON (t.parent_id = a.parent_id AND a.`time` = t.`time`)

 UNION
 SELECT * FROM `articles` WHERE parent_id = 0;

Answer 3

I think you want to do it as two queries and join the results with a UNION. I don't really have enough info from your post to know for sure what it would look like, but maybe something like this:

SELECT parent_id, time FROM articles WHERE parent_id = 0
UNION ALL
SELECT parent_id, MAX(TIME) FROM articles WHERE parent_id > 0
GROUP BY parent_id