SQL get last rows in table WITHOUT primary ID

Question

I have a table with 800,000 entries without a primary key. I am not allowed to add a primary key and I cant sort by TOP 1 ....ORDER BY DESC because it takes hours to complete this task. So I tried this work around:

DECLARE @ROWCOUNT int, @OFFSET int
SELECT @ROWCOUNT = (SELECT COUNT(field) FROM TABLE)
SET @OFFSET = @ROWCOUNT-1


select TOP 1  FROM TABLE WHERE=?????NO PRIMARY KEY??? BETWEEN @Offset AND @ROWCOUNT

Of course this doesn't work.

Anyway to do use this code/or better code to retrieve the last row in table?

Answer 1

If your table has no primary key or your primary key is not orderly... you can try the code below... if you want see more last record, you can change the number in code

Select top (select COUNT(*) from table) * From table
EXCEPT
Select top ((select COUNT(*) from table)-(1)) * From table

Answer 2

I assume that when you are saying 'last rows', you mean 'last created rows'.

Even if you had primary key, it would still be not the best option to use it do determine rows creation order. There is no guarantee that that the row with the bigger primary key value was created after the row with a smaller primary key value.
Even if primary key is on identity column, you can still always override identity values on insert by using set identity_insert on.

It is a better idea to have timestamp column, for example CreatedDateTime with a default constraint. You would have index on this field.
Then your query would be simple, efficient and correct:

select top 1 *
from MyTable
order by CreatedDateTime desc

If you don't have timestamp column, you can't determine 'last rows'.