CODEWITHSUNDEEP
sqlsql-server
mHelpMe
mHelpMe

Reputation: 6668

using row_number to return specific rows of query

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

Upvotes: 0

Views: 253

Answers (3)

Dave Evans
Dave Evans

Reputation: 26

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;

Upvotes: 0

mohan111
mohan111

Reputation: 8865

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

Upvotes: 0

Kaf
Kaf

Reputation: 33809

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

Upvotes: 3

Related Questions