CODEWITHSUNDEEP
mysqlcountsubquery
angelique000
angelique000

Reputation: 909

get subquery and count as colomn

I've a blog system with a simple version control system. My DB is:

|id------|parent_id|title------------------|
|1       |1        |Test title             |
|2       |1        |Test title 2           |
|3       |1        |Last test title        |
|4       |4        |Another blog item      |

My query to get only the lastest versions (only 3 and 4) is:

SELECT * FROM 
(SELECT * FROM blogs ORDER BY id DESC) b
GROUP BY b.parent_id

Result:

My question is: is it possible to count the number of all sub versions?, like:

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

You query is not guaranteed to work. You can read about MySQL group by extensions here. Basically, the fields produced by the * are not guaranteed to come from the "first" record. I want to stress that this is documented not to work, even though it might work in practice.

An alternative way to write your query is:

select b.*, p.numchildren
from (select parent_id, max(id) as maxid, count(*) as numchildren
      from blogs
      group by parent_id
     ) p join
     blogs b
     on b.id = p.maxid
order by b.parent_id

Upvotes: 1

Related Questions