Getting just the last "batch" of values in SQL

Question

Considering a table with:

common_id     id     result 
10            1      8
10            2      10
10            3      4
10            1      7
10            2      5
10            3      2

I want just the last "batch" -- in this case, the last 3 results - but the number of ids in the batch can be different each time (*per a certain common_id). You can assume that each "batch" has the same ids, their order might be different though. So a "batch" is a series of ids that ends with a row having an id that already appeared (again, a batch is per a common_id).

What query can achieve this?

SQL tables represent *unordered* sets. There is no "last" batch unless you have a column that contains the ordering. — Gordon Linoff, Jan 05 '21 at 17:52
@GordonLinoff A set implies distinct members. An SQL table doesn't require rows to be distinct, right? — nicomp, Jan 05 '21 at 17:55

score 0 · Answer 1 · answered Jan 06 '21 at 17:10

Answering my own question (which may be useful to others);

With the help of this answer, what I did was add a new column, which we'll call batch_id - it has the same unique number for all rows in a "batch" and it's always in incrementing order.

E.g.:

common_id     id     result    batch_id
10            1      8         1
10            2      10        1
10            3      4         1
10            1      7         2
10            2      5         2
10            3      2         2

The query to get just the rows of the "latest batch" is:

SELECT * FROM table
JOIN (
    SELECT DISTINCT batch_id
    FROM table
    WHERE common_id = 10
    ORDER BY batch_id DESC LIMIT 1
) latest
ON table.batch_id
IN (latest.batch_id)

Getting just the last "batch" of values in SQL

1 Answers1