CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
user3266538
user3266538

Reputation: 11

How output distinct number of rows in SQL Server 2012

I have a query that results in more than 1 million of records in result table.

But I have to use Excel for further processing and it has a limit for such big data.

How can I query e.g. first 500.000 records and then the last?

I use SQL Server 2012.

Thanks in advance!

Upvotes: 1

Views: 587

Answers (3)

jpw
jpw

Reputation: 44901

You could use the OFFSET FETCH clause like this:

/* Fetch the first 500k rows */
SELECT col1, col2, ... 
FROM TheTable 
ORDER BY Col1 OFFSET 0 ROWS FETCH NEXT 500000 ROWS ONLY;

/* Fetch the next 500k rows */
SELECT col1, col2, ... 
FROM TheTable 
ORDER BY Col1 OFFSET 500000 ROWS FETCH NEXT 500000 ROWS ONLY;

You will have to trim the values used for offset and fetch next to suit the number of rows you want to limit each query by and add extra fetch queries if you need to fetch more than the 1 million in my example.

Upvotes: 6

Sparky
Sparky

Reputation: 15105

You can use 

select TOP 500000 * from table order by id  -- to get the first half..

The bottom half requires knowing how they are ordered.. Assume ID is an ordering field

select * from
 (select top 500000 * from table order by id DESC) xx
order by xx.id

Upvotes: 1

ElGavilan
ElGavilan

Reputation: 6914

SELECT TOP 500000 * FROM table_name

If you want to get the bottom-most values in the table, do an ORDER BY column_name ASC or DESC

Upvotes: -3

Related Questions