Select Top 1000 Suggestion

Question

Post reply

Select Top 1000 Suggestion

michael.horn

Old Hand

Points: 365
More actions
December 7, 2010 at 3:03 pm

#232336

This past weekend i upgraded my sql 2000 server to sql 2008. It looks like everything upgraded correctly except for one view i created in 2000. What this view did was display the top 1000 records just entered. Some how in 2008 in this table it displays the oldest data in this table which is not what i want. I've even used the option Select Top 1000 Rows in the menu and the results are the same. I've tried the LAST command but it doesn't like that. Could someone give me tips on how to display the last 1000 rows entered?
Thank you,
MH

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

CREATE VIEW LastEnteredRecords

AS

SELECT TOP (1000) ...

FROM SomeTable

ORDER BY <column containing date row inserted> DESC

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

michael.horn Old Hand Points: 365 More actions · Answer 2

That gives me the last rows that either have no date in them or dates someone entered in 1900. The data and dates are entered by a machine which has the correct date set, so this weeks data should be in there and displayed. Very strange.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

michael.horn (12/7/2010)
That gives me the last rows that either have no date in them or dates someone entered in 1900. The data and dates are entered by a machine which has the correct date set, so this weeks data should be in there and displayed. Very strange.

That's odd, what Gail posted to you should work.

Can you double check the table and make sure the dates you're looking for transferred?

IE: SELECT DISTINCT <datefield> FROM <table>

Let's make sure the data you want actually is hanging around. 🙂

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Huh? Sure you put DESC? What you describe is what you'll get if you omit the DESC (then it sorts oldest-newest)

Otherwise post table defs and sample data.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

Unless the table has a defined IDENTITY column, or you've come up with your own sequencing scheme, the system has no way of magically tracking which rows were inserted last.

Even if you store a time-stamp in a column, you can get multiple rows that have the exact same date and time, and there are NO guarantees that entry sequence is preserved when you run a select.

Like Gail said, sample schema and data please?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

michael.horn Old Hand Points: 365 More actions · Answer 6

Your right guys, i forgot to put DESC on the end. Sorry for the trouble and thank you for the quick responses.