请尝试以下查询：

select @rn := 1, @itemLag := '';

select rowid, item, itemTitle from (
    select rowid, 
           case when @itemLag = item then @rn := @rn + 1 else @rn := 1 end rn, 
           @itemLag := item, 
           item, 
           itemTitle
    from tbl
    order by item, rowid
) a where rn <= 2

Demo

这是一个最适合分析函数的问题，比如行号和排名。但是，由于您的MariaDB版本不支持它们，我们必须找到一个解决方法。
一种不需要任何动态SQL的方法是创建一个查询，该查询查找每个项目组的最小值和次小值rowid。然后，我们只需将原始表连接到该子查询，以便仅保留前两个匹配行。

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT a.item, b.min_rowid, MIN(a.rowid) AS next_min_rowid
    FROM yourTable a
    INNER JOIN
    (
        SELECT item, MIN(rowid) AS min_rowid
        FROM yourTable
        GROUP BY item
    ) b
        ON a.item = b.item
    WHERE a.rowid > b.min_rowid
    GROUP BY a.item, b.min_rowid
) t2
    ON t1.item = t2.item AND (t1.rowid = t2.min_rowid OR t1.rowid = t2.next_min_rowid)
ORDER BY
    t1.item, t1.rowid;

Demo（第一个字母）

我将使用带有LIMIT子句的相关子查询：

SELECT t.*
FROM table t
WHERE rowid IN (SELECT t1.rowid 
                FROM table t1
                WHERE t1.item = t.item
                ORDER BY t1.itemtitle
                LIMIT 2 
               );

如果您需要最新的itemtitle，则执行ORDER BY t1.itemtitle DESC，并使用10或n修改LIMIT子句。

假设每个项目至少有两行，这将是可行的：

select t.*
from t
where t.rowid <= (select t2.rowid
                  from t2
                  where t2.item = t.item
                  order by t2.rowid
                  limit 1, 1
                 );

您可以使用coalesce()来行程只有一列的项目：

select t.*
from t
where t.rowid <= coalesce( (select t2.rowid
                            from t2
                            where t2.item = t.item
                            order by t2.rowid
                            limit 1, 1
                           ),
                           t.rowid
                         );

select 
  *,
  row_number() over (partition by item order by rowid asc) as rank 
  from yourtable
  qualify rank <= 2