CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Ajinkya Rahane
Ajinkya Rahane

Reputation: 49

Efficient way to write this query

I am trying to order the records by 3 columns and then select a particular ID and the record before that plus the row after that. Here is my query:

;With Cte As
(
    SELECT ROW_NUMBER() Over(Order By Book, PageINT, [IDAuto]) as RowNum,  [IdAuto]

      FROM CCWiseInstr2
)
Select * From Cte
    Where RowNum = (Select RowNum From Cte
    Where IdAuto = 211079)
        UNION
Select * From Cte
    Where RowNum = (Select RowNum - 1 From Cte
    Where IdAuto = 211079)
    UNION
Select * From Cte
    Where RowNum = (Select RowNum + 1 From Cte
    Where IdAuto = 211079)

What could the other efficient way to write this query. At the moment the query takes about 336 ms after creating all indexes which looks like a bit higher to me.

Here is the plan for the query:

http://gyazo.com/9a7f1c37d4433665d0949acf03c4561c

Any help is appreciated.

Upvotes: 0

Views: 78

Answers (2)

TCM
TCM

Reputation: 16900

How about this query:

;With Cte As
(
    SELECT ROW_NUMBER() Over(Order By Book, PageINT, [IDAuto]) as RowNum,  [IdAuto]       
        FROM CCWiseInstr2
)
Select RowNum, IDAuto From Cte
    Where RowNum IN ( 
        Select RowNumber  From 
            (
                Select  RowNum - 1 as RowNumPrev, 
                        RowNum as RowNum, 
                        RowNum + 1 as RowNumNext 
                        From Cte
                            Where IdAuto = 211079 
            ) vw unpivot ( 
                            RowNumber For 
                                    IdAuto IN (RowNumPrev, RowNum, RowNumNext ) 
                         ) unpw )

Instead of UNION just use UNPIVOT which will convert your columns into rows which you could then use in IN. Let me know how it goes.

Upvotes: 2

Vinnie
Vinnie

Reputation: 3929

You can use the LEAD and LAG functions with SQL Server. Here's a great article on Simple Talk covering all of the options. (Code below is untested)

https://www.simple-talk.com/sql/t-sql-programming/sql-server-2012-window-function-basics/

SELECT
  [IdAuto],
  LAG([IDAuto], 1) OVER(Order By Book, PageINT, [IDAuto]) AS PreviousSale,
  LEAD([IDAuto], 1) OVER(Order By Book, PageINT, [IDAuto]) AS NextSale
FROM
  CCWiseInstr2
WHERE [IdAuto] = 211079;

Upvotes: 2

Related Questions