MySQL Select rows on first occurrence of each unique value

mysql has a "cheat" for this:

select *
from mytable
group by cid;

That's all you need, because in mysql it allows you to not aggregate the non-grouped-by columns (other databases would throw a syntax error), in which case it outputs only the first occurrence of each group-by value(s). Note though that this won't guarantee the way in which the "first" occurrence is determined (it will be just how the rows are read in)

If you want a particular first occurrence, sort first, then apply the group-by cheat:

select *
from (
    -- order by the "time" column descending to get the "most recent" row
    select * from mytable order by time desc
    ) x
group by cid

Try this one,

SELECT *
FROM tableName a 
INNER JOIN (
    SELECT cid, MIN(`time`) AS MinTime
    FROM tableName
    GROUP BY cid
) b 
ON a.CID = B.cid AND a.time = b.MinTime

In MySQL 8, you would use window functions for this

SELECT cid, pid, rid, clink, time, snippet
FROM (
  SELECT t.*, ROW_NUMBER() OVER (PARTITION BY cid ORDER BY time) rn
  FROM t
) t
WHERE rn = 1

You could use a filtering join:

select  *
from    (
        select  cid
        ,       min(time) as min_time
        from    YourTable
        group by
                cid
        ) filter
join    YourTable yt
on      filter.cid = yt.cid
        and filter.min_time = yt.time

MySQL Select rows on first occurrence of each unique value

Related

Recent Posts