Correlated subquery with row number count

Question

I have a table as follows and what I want is to use get the initial row with least id of each uid group.

The table is as follows

_id  uid  type 
1     a    a
2     b    bbb   #satisfied
3     b    ccc
4     b    aaa   #satisfied
5     a    aaa   #satisfied
6     b    eee

I can already get the initial row using the following correlated subquery

SELECT *
FROM table
WHERE _id IN (
               SELECT MIN(_id) 
               FROM table 
               WHERE type IN ('aaa','bbb')
               GROUP BY uid
             );

However, I want the 4th column shown the count of rows satisfied the condition (type IN ('aaa','bbb')), as cnt shown below:

_id  uid  type  cnt
5     a    aaa   1
2     b    bbb   2

I think I can count this use several joins and then join the result to my code...But this is ugly...Is there any elegant way to achieve this...

Answer 1

You can try this:

SELECT t1.*, t2.cnt
FROM table t1 INNER JOIN (
  SELECT MIN(_id) AS id, COUNT(_id) AS cnt
  FROM table 
  WHERE type IN ('aaa','bbb')
  GROUP BY uid
) t2 ON t1._id = t2.id
ORDER BY t1.uid