using row_number to return specific rows of query

Question

I am using SQL Server 2012 & MATLAB. I have a table of 5 columns (1 char, 1 datetime and 3 floats). I have a simple query shown below that returns the data from this table which contains over a million records - this however causes an out of memory error in MATLAB.

simple query

 select id_co, date_r, FAM_Score, FAM_A_Score, FAM_Score
 from GSI_Scores
 where id_co <> 'NULL'
 order by id_co, date_rating

So I was looking to breakdown the query select the data in batches of 250,000 records. I have just come across the ROW_NUMBER function which I added to my query, please see below. This numbers all the records for me. However I am having trouble selecting say records between 250,000 and 500,000. How do I do this?

updated query

  select id_co, date_r, FAM_Score, FAM_A_Score, FAM_Score, row_number() over (order by id_co) as num_co
 from GSI_Scores
 where id_co <> 'NULL' and num_sedol between 250000 and 500000
 order by id_co, date_rating

Have you considered using a cursor? I know they're supposed to be a no-no, but they're great for working with large amounts of data. — Captain Kenpachi
– Captain Kenpachi, Commented Jul 30, 2014 at 7:58

Indi · Accepted Answer · 2014-07-30 07:55:29Z

3

Simply use a sub query or Common Table Expression (CTE).

;WITH CTE AS
(
  --Your query
)
SELECT * FROM CTE
WHERE num_co BETWEEN 250000 AND 500000

answered Jul 30, 2014 at 7:55

Indi

33.9k7 gold badges61 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mohan111 · Accepted Answer · 2014-07-30 08:04:36Z

Just an sample example

 declare @t table  (ID INT)
 insert into @t (id)values (1)
  insert into @t (id)values (2)
   insert into @t (id)values (3)
  insert into @t (id)values (4)

  ;WITH CTE AS
  (
  select *,COUNT(ID)OVER (PARTITION BY ID  ) RN from @t

 )
 Select ID from CTE C WHERE C.ID BETWEEN 2 AND 4 
 ORDER BY RN

OR

;WITH CTE (select id_co,
                        date_r, 
                        FAM_Score,
                        FAM_A_Score, 
                        FAM_Score, 
                        COUNT(id_co) over (PARTITION BY ID  DESC) as num_co
                from GSI_Scores
                    where id_co <> 'NULL')
            Select C.d_co,
            C.date_r, 
            C.FAM_Score,
            C.FAM_A_Score, 
            C.FAM_Score FROM CTE C
            WHERE C.id_co between 250000 and 500000
            order by C.id_co, C.date_rating

Dave Evans · Accepted Answer · 2014-07-30 13:56:53Z

0

You could try using the OFFSET x ROWS FETCH NEXT y ROWS ONLY commands like this:

CREATE TABLE TempTable (
    TempID INT IDENTITY(1,1) NOT NULL,
    SomeDescription VARCHAR(255) NOT NULL,
    PRIMARY KEY(TempID))

INSERT INTO TempTable (SomeDescription) 
VALUES ('Description 1'),
    ('Description 2'),
    ('Description 3'),
    ('Description 4'),
    ('Description 5'),
    ('Description 6'),
    ('Description 7'),
    ('Description 8'),
    ('Description 9'),
    ('Description 10')

SELECT * FROM TempTable ORDER BY TempID OFFSET 3 ROWS FETCH NEXT 2 ROWS ONLY;

answered Jul 30, 2014 at 13:56

Dave Evans

264 bronze badges

Collectives™ on Stack Overflow

using row_number to return specific rows of query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related