Read last entry of table

Hi All,<br /><br />This might be a very primary question for you.<br /><br />I want to read last entry of the table. Suppose I have table as,<br /><br />ID Name<br />1 a<br />2 b<br />3 c<br />4 d<br /><br />and this table is dynamic. I want to read last name i.e. 'd'. <br /><br />I wrote a query like, <br />select Name from table where ID = (select max(ID) from table)<br /><br />But this takes considerably long time. Is there any fastest way for this? I will be using this quite frequently so cannot afford delay. What will be the faster way?<br /><br />Thanks in advance. <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />Chioo.

Have you got an index on ID? It should be fast if you have.

Thats right, create an index on ID column of the dynamic table and then it would be faster

If ID is gonna be in ever increasing order, then this might work faster...


SELECT Name
FROM Table
WHERE ID = (SELECT max(rowcnt)
FROM sysindexes
WHERE object_name(id) = 'Table')

or

SELECT Name
FROM Table
WHERE ID = (SELECT count(*)
FROM Table)

Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."

Add an index on id column as recommended and try:


select top 1 name
from table
order by id desc

Adding index will make your query fast, the query above may be slightly faster but I don't know if the execution time difference would be measurable at all.

Ramkumar, both your queries may return incorrect results and the second query would perform the slowest of all queries posted in the thread. The problem is that some rows might be deleted so last ID would be greater then row count.

<br />Thanks a lot for your suggestions. <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />I have a doubt regarding index. As ID is primary key of my table, I think it is already indexed. Will creating another index affect performance?<br /><br />I forgot to mention I am using MySQL. <br /><br />Chioo.

I don't know about MySQL, this is MSSQL Server forum.

<br />Sorry. My mistake. [<img src='/community/emoticons/emotion-6.gif' alt='' />] I meant to say MSSQL only.

Ah, so it is mssql? Then you don't need additional index, however it is strange that your query is slow.

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Interesting what happens at almost the same time at three distinct places of the world. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">[<img src='/community/emoticons/emotion-1.gif' alt='' />] See, I improved my typing speed [<img src='/community/emoticons/emotion-5.gif' alt='' />]

<br />Yes. I am using SQL server. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />I am new in SQL so sometimes get confused of terminology.