Selecting all records except the top 60000

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 2005 Forums

Transact-SQL (2005)

Selecting all records except the top 60000

Author

Topic

kieranhodges
Starting Member

2 Posts

Posted - 2007-10-18 : 08:47:48

Can anyone help please?

I want to use SQL to extract some data from a table containing 96000 records. I just need the records from 60001 to 96000 is there a way to select the NOT(TOP 60000)??

There is no PK on the records to sort and select.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-18 : 09:04:39

If there is no PK/unique key, how will you define top 60000 records?
Any random records?

Madhivanan

Failing to plan is Planning to fail

kieranhodges
Starting Member

2 Posts

Posted - 2007-10-18 : 09:08:49

There is no PK field.

It is literally 96000 lines of random raw data just selected on the order it as it appears in the table.

I want to extract rows 1-60000 into a Excel worksheet and the rest into a seperate worksheet using 2 seperate queries.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-18 : 09:26:10

set rowcount 60000
select *
from yourTable
order by SomeColumn

set rowcount 30000
select *
from yourTable
order by SomeColumn desc

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Kristen
Test

22859 Posts

Posted - 2007-10-18 : 10:11:21

"selected on the order it as it appears in the table[/]b."

No such thing in a relational database I'm afraid. Without an explicit ORDER BY there is no guaranteed repeatable order. If SQL Server has part of the table lying around in memory, from someone else's query, that can effect the order in which the data is processed, and output [in the absence of ORDER BY]

You easiest route, if you want a [b]repeatable sequence s to add an IDENTITY column, and put a Clustered PK on that.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources