Set RowCount (SQL 2k5) 1

[Catadmin]

Okay,

This is my first time working with the new Set RowCount option. Looks like fun and I understand the basics, but I have a question.

I have a record set returning 409934 Distinct rows. Of course, the end users want this in an excel sheet (at which point I laughed hysterically before digging out the new RowCount function info). So, I can get the first 65,000 rows (I believe that's Excel's limit) into a sheet, but now I'm trying to figure out how to get the next 65,000 into a new worksheet. Can I do a WHERE RowCount > 65,000 and RowCount < 130,000?

For the record, I did try this and am getting an error, but I don't know if there's a way around this other than sticking everything in a TEMP table and using an identity column to separate the values.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLDenis]

>>new Set RowCount option


???? this is old and will be deprecated in the future
what is new in sql server 2005 is that you can use TOP with a variable

select top @Rowcount from table ...

you probably want ROWNUMBER()

here is an example of hot to use it

CREATE TABLE Rankings (Value Char(1),id INT)
INSERT INTO Rankings
SELECT 'A',1 UNION ALL
SELECT 'A',3 UNION ALL
SELECT 'B',3 UNION ALL
SELECT 'B',4 UNION ALL
SELECT 'B',5 UNION ALL
SELECT 'C',2 UNION ALL
SELECT 'D',6 UNION ALL
SELECT 'E',6 UNION ALL
SELECT 'F',5 UNION ALL
SELECT 'F',9 UNION ALL
SELECT 'F',10



ROW_NUMBER()
This will just add a plain vanilla row number

SELECT ROW_NUMBER() OVER( ORDER BY Value ) AS 'rownumber',*
FROM Rankings


The following one is more interesting, besides the rownumber the Occurance field contains the row number count for a given value
That happens when you use PARTITION with ROW_NUMBER

SELECT ROW_NUMBER() OVER( ORDER BY value ) AS 'rownumber',
ROW_NUMBER() OVER(PARTITION BY value ORDER BY ID ) AS 'Occurance',*
FROM Rankings
ORDER BY 1,2


This is just ordered in alphabetical order descending

SELECT ROW_NUMBER() OVER( ORDER BY Value DESC) AS 'rownumber',*
FROM Rankings

then you can do something like this

select * from(
SELECT ROW_NUMBER() OVER( ORDER BY Value ) AS 'rownumber',*
FROM Rankings )z 
where rownumber between 1 and 5

select * from(
SELECT ROW_NUMBER() OVER( ORDER BY Value ) AS 'rownumber',*
FROM Rankings )z 
where rownumber between 6 and 10

more here

http://sqlservercode.blogspot.com/2006/03/rownumber-ntile-rank-and-denserank.html

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Catadmin]

Aha! I knew there was a new feature.. I just apparently didn't remember the correct one.

Thanks, Denis. This helps a lot.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

Yes, Virginia, I really did have to use the funky WITH <QueryName> AS for this to work..

WITH MyQuery AS
(Select Row_Number() Order(Order by MyColumn) as RowNumber,
col1, col2, col3, col4...
from MyTable
Where MyCondition = 1)

Select *
from MyQuery
where RowNumber > 65000 and RowNumber <= 130000

Worked PERFECTLY. I ported it over into the SSIS Export Wizard and it did almost everything I wanted it to do EXCEPT it didn't allow me to pick a sheet to export my data to. Which means if I want 1 spreadsheet with multiple sheets, I have to cut-n-paste from multiple excel reports into one.

Does anyone know a way to specify sheets in the SSIS export wizard?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[philhege]

You'll have to use an SSIS project, not the Wizard, since this will require multiple data adapters (one for each worksheet).

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[Catadmin]

Awww, gosh darnit. I was afraid of that.

I'm running adhoc reports and don't have time to mess with creating an SSIS package. Guess I'll just port it all to a text file and make the end user deal with it.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[philhege]

Or, you could probably stitch together a couple of the Wizard packages. I hope SSIS still has the "Run DTS Package" task ;^)

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.