SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

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

ptegan
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?

gpl
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

madhivanan
Premature Yak Congratulator

India
22460 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

Madhivanan

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

ptegan
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

mr_mist
Grunnio

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

ptegan
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

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts
Posted - 06/06/2005 :  10:20:10  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
Congrats on getting married!!!
[great timing]
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50323&whichpage=5
[/great timing]

anyway...

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

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



Corey

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  
 New 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.06 seconds. Powered By: Snitz Forums 2000