Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
ptegan
Starting Member
7 Posts |
Posted - 2005-06-06 : 06:27:40
|
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
195 Posts |
Posted - 2005-06-06 : 06:45:13
|
yes, use top but order it descending (I assume you are using a datetime to log when the row was added) |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-06-06 : 06:47:17
|
If the table has Datetime column then you can write a querySelect top 100 * from yourTable order by DatetimeCol DescMadhivananFailing to plan is Planning to fail |
|
|
ptegan
Starting Member
7 Posts |
Posted - 2005-06-06 : 09:14:09
|
Thanks for the replies.My problem is thatSelect top 100 * from yourTable order by DatetimeCol Descresults in a timeout."Select top 100 * from yourTable" is fine though.What I need is a "Select BOTTOM 100 * from yourTable" command :-) |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2005-06-06 : 09:53:17
|
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. :) |
|
|
ptegan
Starting Member
7 Posts |
Posted - 2005-06-06 : 10:08:55
|
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! :) |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-06-06 : 10:20:10
|
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) Awhere A.string LIKE '%TEXT%' CoreySecret Service Agent: Mr. President, you're urinating on me.President Lyndon Johnson: I know I am. It's my prerogative. |
|
|
|
|
|