Selecting LAST entries - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Selecting LAST entries

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 query

Select top 100 * from yourTable order by DatetimeCol Desc

Madhivanan

Failing 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 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 :-)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources