How to get the last 3 different identifiers from mysql table

Question

Ok, so basically I have a database table. The first column is an identifier. The second is pkg_id. The third is not important, and the fourth is the previous identifier in which pkg_id was located. I need to pull the last 3 pkg_id from the table. So basically I need to pull out the last 3 17879 pkg_id and the last 3 3075. So in this example I need to output id 9, 7, 6 for 17879 and id 8, 5, 3 for 3075.

I can not get around it. I have access to the previous identifier that was. So you see that for id 9 it says that 17879 was the last in id 7. This id 8 was the last in id 5.

- , . Java , mysql. .

Answer 1

SELECT  m.*
FROM    (
        SELECT  pkg_id,
                COALESCE(
                (
                SELECT  id
                FROM    mytable mi
                WHERE   mi.pkg_id = md.pkg_id
                ORDER BY
                        id DESC
                LIMIT 2, 1
                ), 0) AS mid
        FROM    (
                SELECT  DISTINCT pkg_id
                FROM    mytable
                ) md
        ) q
JOIN    mytable m
ON      m.pkg_id <= q.pkg_id
        AND m.pkg_id >= q.pkg_id
        AND m.id >= q.mid

mytable (pkg_id, id), .

: m.pkg_id <= q.pkg_id AND m.pkg_id >= q.pkg_id m.pkg_id = q.pkg_id. .