How to check if the current row is the last selected row in a sql query?

Question

I have a short question! I am selecting a number of rows as simple as the following:

SELECT * FROM ... WHERE ... GROUP BY ...

I would like to add another field/attribute to results sets, let's say last, which determines if the current row is the last one in the results set. I would like the results set look like the following?

id    name   last
---   ----   ----
12    foo    false
20    smth   false
27    bar    true

Answer 1

You need to have a sort order to make the last record determinate. Using either the MAX() function for ASC sort order or MIN() for DESC you can test for equality to determine if the value of the sort order field is that of the last record in the resultset, at least when the sort field is a unique key. For example, in your query it looks like the sort order is on the ID field, which would normally be a unique key.

SELECT id, field1, field2, id=MAX(id) AS last_record FROM my_table ORDER BY id ASC;

Answer 2

1. You have to specify ORDER BY to define some sorting of the rows.
2. I believe any solution for MySQL would effectively mean that your query is executed at least twice, so it is really better to do it on the client.

If you are OK with running the same query twice, then do something like this.

Get the ID of the last row first:

SELECT @LastID := ID 
FROM ... 
WHERE ... 
GROUP BY ...
ORDER BY ID DESC
LIMIT 1;

Use that ID in the main query:

SELECT * 
    , CASE WHEN ID = @LastID THEN FALSE ELSE TRUE END AS IsLast
FROM ... 
WHERE ... 
GROUP BY ...
ORDER BY ID ASC;

As is, most likely it will break if the table is updated while these two queries run (the remembered ID may become not the last one if rows are added or deleted, unless you do something to address this issue).

Of course, you can put it all into one query:

SELECT 
    *, 
    CASE WHEN ID = LastID THEN FALSE ELSE TRUE END AS IsLast
FROM 
    YourTable
    CROSS JOIN 
    (
        SELECT ID AS LastID
        FROM YourTable
        ORDER BY ID DESC
        LIMIT 1
    ) AS TableLastID
ORDER BY ID ASC;

You need to check whether MySQL is smart enough to run the inner SELECT LIMIT 1 only once or it will run it for every row of the main table. Still, even in the best case the query is effectively executed twice.