SQL get last rows in table WITHOUT primary ID

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 D开发者_StackOverflow社区ESC 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?

---

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

---

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'.

---

If you need to select 1 column from a table of 800,000 rows where that column is the min or max possible value, and that column is not indexed, then the unassailable fact is that SQL will have to read every row in the table in order to identify that min or max value.

(An aside, on the face of it reading all the rows of an 800,000 row table shouldn't take all that long. How wide is the column? How often is the query run? Are there concurrency, locking, blocking, or deadlocking issues? These may be pain points that could be addressed. End of aside.)

There are any number of work-arounds (indexes, views, indexed views, peridocially indexed copies of the talbe, run once store result use for T period of time before refreshing, etc.), but virtually all of them require making permanent modifications to the database. It sounds like you are not permitted to do this, and I don't think there's much you can do here without some such permanent change--and call it improvement, when you discuss it with your project manager--to the database.

---

You need to add an Index, can you?

Even if you don't have a primary key an Index will speed up considerably the query.

You say you don't have a primary key, but for your question I assume you have some type of timestamp or something similar on the table, if you create an Index using this column you will be able to execute a query like :

SELECT * 
FROM table_name
WHERE timestamp_column_name=(
     SELECT max(timestamp_column_name)
     FROM table_name
)

---

If you're not allowed to edit this table, have you considered creating a view, or replicating the data in the table and moving it into one that has a primary key?

Sounds hacky, but then, your 800k row table doesn't have a primary key, so hacky seems to be the order of the day. :)

---

I believe you could write it simply as

SELECT * FROM table ORDER BY rowid DESC LIMIT 1;

Hope it helps.