CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Robert Harvey
Robert Harvey

Reputation: 180798

Creating and using a temporary table to return a specific row, instead of running the query repeatedly

I have a program where a query is being run repeatedly to get a single row number (here called a "partition").

declare @partition int = {0}

;with candidates as
(
    select column1, column2,
    ROW_NUMBER() over (order by table1.column1) as RowNumber,
    ...
)
select c.column1, c.column2, 
from candidates c
where c.RowNumber - 1 = @partition

Instead of running this query every time I want to get a specific row, I'd like the query to write a temporary table and return the first row if the partition is zero, and return a row from the temporary table if the partition is not zero.

How would I go about doing that? Can it be done in a single query?

Upvotes: 1

Views: 55

Answers (2)

paparazzo
paparazzo

Reputation: 45096

I get not the stated question but why not 

select column1, column2,
...
order by column1 
OFFSET @partition ROWS 
FETCH NEXT 1 ROWS ONLY;

Is this a complex query that you want to cache?

OK cache 

declare @partition int = {0}

IF @partition = 0
begin 
   truncate table TableCache;
   insert into TableCache (column1, column2, rownumber) 
   SELECT column1, column2, 
          Row_number() OVER (ORDER BY table1.column1, column2) AS rownumber 
   ......;
end
select column1, column2 
  from TableCache 
 where rownumber - 1 = @partition;

If this is program it would probably be way more efficient to the read column1, column2 into a List and refer to them from the program

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93724

Why can you do this.

declare @partition int = {0}

IF @partition <> 0 
;WITH candidates AS 
( 
         SELECT   column1, 
                  column2, 
                  Row_number() OVER (ORDER BY table1.column1) AS rownumber 
         ......
 ) 
SELECT c.column1, 
       c.column2, 
FROM   candidates c 
WHERE  c.rownumber - 1 = @partition 
else
SELECT TOP 1 column1, 
         column2, 
FROM     table1 
ORDER BY table1.column1

Upvotes: 1

Related Questions