Select record based on row numbers...

[krishnarao]

I have build a query which returns 200 thousand records, and when trying to export, it only exports 65000 (approx) and the rest are lost. Is there any conventional way to select top 50000 records or between 50000 to 100000?? Thanks in Advance...

 

[KenHigg]

What format are you trying to export to?

 

[maxmangion]

try using the "Top Values" property in your query properties.

 

[redneckgeek]

Based on the 65000 limitation, I assume you are trying to export to Excel.
Are you willing to export to 4 different spreadsheets?

You'll have to do 2 queries.
One pulls the first 50,000 records.
The second will use the NOT IN predicate to pull the next 50000.
You'll have to the nested query after each run.

The first query

SELECT TOP 10 sometable.PRODUCT
FROM sometable
ORDER BY sometable.PRODUCT

The second query

SELECT TOP 10 sometable.PRODUCT
FROM sometable
WHERE sometable.PRODUCT NOT IN (
SELECT TOP 10 sometable.PRODUCT
FROM sometable
ORDER BY sometable.PRODUCT)
ORDER BY sometable.PRODUCT

After each successive run, you'll change the number in the nested query to eliminate records you've already selected.

This will likely run incredibly slow, and if your "key" contains more than one field, you'll have to switch to EXISTS instead of IN.

 

[manoj.mcans]

Hi,

If you are using MS-Office 2003 , then its limitation on Ms-Office 2003 , you can not export more than 65500 (approx) record in on one excel file.

If You are using MS-Office 2007 then you can fetch 1 millon record in excel file

Best and easy way is just take the data in DataSheet view by just running the query intead of exporting the data in Excel sheet.

May this will help you in someway.

Thanks

 

[redneckgeek]

If You are using MS-Office 2007 then you can fetch 1 millon record in excel file

Well that's certainly an improvement! My company is still on XP. We'll upgrade to 2003 in another year or two.

I think, from the original post, that the user is not on 2007 so he'll have to use an alternate method.