The Last Entered Row: Why a Table Needs an Ordering Column

The last entered row does not exist in a SQL Server table until you define it. A table is not a stack, so you need a column that says what last means, and an ORDER BY that uses it.

The question a junior DBA always asks

A junior DBA walks over and asks, “How do I get the last row of this table?” They already tried SELECT TOP (1) with no ORDER BY, and it looked right on the test server. That is exactly the problem. It looked right.

Rows in a table have no position. They have values. If you do not tell SQL Server which value defines last, the engine returns whatever row is cheapest to find. Let me show you with a small events table. It lives in tempdb as a temp table, so there is nothing to clean up.

Build a table with two kinds of time

The table has an identity column, the time the event happened, and the time we loaded it. Those are two different clocks, and that matters in a minute. The three starter rows go in with one INSERT statement.

DROP TABLE IF EXISTS #Events;

CREATE TABLE #Events
(
    EventId   int IDENTITY(1,1) PRIMARY KEY,
    EventTime datetime2(0) NOT NULL,
    LoadedAt  datetime2(7) NOT NULL DEFAULT SYSUTCDATETIME(),
    Payload   nvarchar(50) NOT NULL
);

INSERT #Events (EventTime, Payload)
VALUES ('2026-10-01 09:00:00', N'login'),
       ('2026-10-01 09:05:00', N'search'),
       ('2026-10-01 09:10:00', N'checkout');

One table, two answers

Now a late event arrives. A mobile app was offline, and it reports a purchase from 9:07 AM after everything else is stored. Ask for the last row by identity, then by event time.

INSERT #Events (EventTime, Payload)
VALUES ('2026-10-01 09:07:00', N'late purchase');

SELECT TOP (1) EventId, EventTime, Payload
FROM #Events
ORDER BY EventId DESC;

SELECT TOP (1) EventId, EventTime, Payload
FROM #Events
ORDER BY EventTime DESC, EventId DESC;

The first query returns the late purchase, event 4. It is the last row entered. The second returns checkout, event 3. It is the last thing that happened. Both answers are correct. They answer different questions, so decide which one your report needs.

Break the ties on purpose

Timestamps tie more often than you would think. The three starter rows went in with one statement, so they share a single LoadedAt value. The first query counts them. The second shows how a tie breaker gives you one stable answer.

SELECT COUNT(*) AS rows_loaded, COUNT(DISTINCT LoadedAt) AS distinct_loaded_at
FROM #Events
WHERE EventId <= 3;

SELECT TOP (1) EventId, Payload
FROM #Events
WHERE EventId <= 3
ORDER BY LoadedAt DESC, EventId DESC;

Three rows, one distinct timestamp, and the tie breaker returns event 3. Without EventId DESC as the second sort key, any of the three would be a legal answer, and the answer could change from one run to the next. The identity column is a good tie breaker because it is unique.

Why a query without ORDER BY proves nothing

Here is the trap. Run TOP (1) with no ORDER BY, add a harmless index, and run it again.

SELECT TOP (1) Payload FROM #Events;

CREATE INDEX IX_Events_Payload ON #Events (Payload);

SELECT TOP (1) Payload FROM #Events;

The first result is login, the first row entered. After the index, the same query returns checkout. The engine found a narrower index to read, and that index happens to be sorted by Payload. Nobody changed the data. A clustered index does not promise order either. It is just how the table is stored.

Identity gaps and commit order

Identity is not gap free. A rolled-back insert still uses up a number. Run this and look for the missing value.

BEGIN TRANSACTION;
INSERT #Events (EventTime, Payload) VALUES ('2026-10-01 09:12:00', N'rolled back');
ROLLBACK;

INSERT #Events (EventTime, Payload) VALUES ('2026-10-01 09:15:00', N'cart cleared');

SELECT EventId, Payload FROM #Events ORDER BY EventId;

Events 1 to 4 are there, then 6. Number 5 went to the rolled-back row. Gaps are harmless for finding the last row. What bites is concurrency: two sessions can get their numbers in one order and commit in the other. If your job says “process everything after the last id I saw,” a slow transaction can slip through behind it.

Index the ordering you chose

Once you pick your rule, back it with an index that matches the ORDER BY. On a large table that lets SQL Server read one row at the end of the index instead of sorting everything. The query returns event 6, cart cleared, and the last line drops the table.

CREATE INDEX IX_Events_EventTime ON #Events (EventTime DESC, EventId DESC);

SELECT TOP (1) EventId, Payload
FROM #Events
ORDER BY EventTime DESC, EventId DESC;

DROP TABLE IF EXISTS #Events;

Write down what last means before anyone builds a report on it.

The last row is not a table position, it is your ordering rule.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.