Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Selecting LAST entries
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

7 Posts

Posted - 06/06/2005 :  06:27:40  Show Profile  Reply with Quote

I have a table that's about 2Gb in size and information is constantly being written to it. I need to run a little script on the hour that searchs for a specific string in a specific column but due to the size I though I'd take a sample of maybe the last 100 entries or something.

I'm looking for a way to easily access the last entries added to the table. something like the TOP command but the opposite :)

Is there an easy way of doing this that I'm missing?

Posting Yak Master

United Kingdom
195 Posts

Posted - 06/06/2005 :  06:45:13  Show Profile  Reply with Quote
yes, use top but order it descending (I assume you are using a datetime to log when the row was added)
Go to Top of Page

Premature Yak Congratulator

22864 Posts

Posted - 06/06/2005 :  06:47:17  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
If the table has Datetime column then you can write a query

Select top 100 * from yourTable order by DatetimeCol Desc


Failing to plan is Planning to fail
Go to Top of Page

Starting Member

7 Posts

Posted - 06/06/2005 :  09:14:09  Show Profile  Reply with Quote
Thanks for the replies.

My problem is that

Select top 100 * from yourTable order by DatetimeCol Desc

results in a timeout.

"Select top 100 * from yourTable" is fine though.

What I need is a "Select BOTTOM 100 * from yourTable" command :-)

Go to Top of Page


United Kingdom
1870 Posts

Posted - 06/06/2005 :  09:53:17  Show Profile  Visit mr_mist's Homepage  Reply with Quote
Is your datetimecol indexed? If not, then you might need to consider indexing it if this is the sort of query that you want to run.

Moo. :)
Go to Top of Page

Starting Member

7 Posts

Posted - 06/06/2005 :  10:08:55  Show Profile  Reply with Quote
Ahh, yeah, I didn't think of that. All the indexes in the word created on that table except the right one :)

Just so that I'm not wasting my time here...

I'm trying to lookat the last 50 lines entered to the table on every hour and search them for a specific string. So a Select TOP 100 * from yourTable order by DatetimeCol Desc where string LIKE '%TEXT%' and someone combine that into a count() to test if I get a return or not.

Does that make any sense (getting married in 2 days...difficult to concentrate! :)

Edited by - ptegan on 06/06/2005 10:09:17
Go to Top of Page

Flowing Fount of Yak Knowledge

2878 Posts

Posted - 06/06/2005 :  10:20:10  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
Congrats on getting married!!!
[great timing]
[/great timing]


you need to select the group of records before you do add a where clause like that:

Select count(*)
	(Select TOP 100 * from yourTable order by DatetimeCol Desc) A
where A.string LIKE '%TEXT%'


Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000