mysql query to retrieve first 2 records for each item type

user_4302791 0
Viewed 836

I have a table like

rowid    item      item title
1        pen       pen 1
2        pen       pen 2
3        pencil    pencil 1
4        pencil    pencil 2
5        pen       pen 3
6        paper     paper 1
7        pencil    pencil 3
8        paper     paper 2
9        paper     paper 3

I need a query that can output like this in mysql

rowid    item      item title
1        pen       pen 1
2        pen       pen 2
6        paper     paper 1
8        paper     paper 2
3        pencil    pencil 1
4        pencil    pencil 2

I mean for each distinct item the query need to output first 2 item titles also please give me the query

to get last 10 or n rows for each item instead of first 2 rows

mariadb greatest-n-per-group sql
5 Answers

Try this query:

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

user_71325500

This is a problem which is best suited for analytic functions like row number and rank. But, since your version of MariaDB does not support them, we have to find a workaround.

One approach, which does not require any dynamic SQL, is to create a query which finds the minimum and second-to-minimum rowid values for each group of items. Then, we simply join the original table to this subquery to retain only the first two matching rows.

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

user_18632290

I would use correlated subquery with LIMIT clause :

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 
               );

If you want latest itemtitle then do ORDER BY t1.itemtitle DESC & modify LIMIT clause with 10 or n.

user_55294450

This will work, assuming that there are at least two rows for each item:

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

You can handle items with only one row by using 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
                         );
user_11440350 
select 
  *,
  row_number() over (partition by item order by rowid asc) as rank 
  from yourtable
  qualify rank <= 2
user_17575110
Related