CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
slayer35
slayer35

Reputation: 604

Common table expression and pivot

Iam trying on a sql query but i cant do something.I want to do paging by using row_number in this query but i cant figure out what will i have to put "row_number between" statement.Any help is appreciated.

  WITH cte_ana
     AS (SELECT movie_id,
                id,
                tag_id
         FROM   dt_movieTag)
SELECT Kayit.*,
       ROW_NUMBER() OVER (ORDER BY movie_id) as RowNum
FROM   cte_ana PIVOT(MAX(id) FOR tag_id IN ([3], [5], [9]))AS Kayit
where  Kayit.[3] is not null
       and kayit.[5] is not null
       and kayit.[9] is not null
order  by movie_id

For example i want to select only few data from this query by this (where RowNum between 0 and 25 )

Upvotes: 0

Views: 472

Answers (1)

Martin Smith
Martin Smith

Reputation: 453243

You can't use row_number in the where clause so need to nest CTEs. (Obviously you could just use TOP (25) instead if you want the first 25 rows).

WITH cte_ana
     AS (SELECT movie_id,
                id,
                tag_id
         FROM   dt_movieTag),
cte2 AS
(         
SELECT Kayit.*,
       ROW_NUMBER() OVER (ORDER BY movie_id) as RowNum
FROM   cte_ana PIVOT(MAX(id) FOR tag_id IN ([3], [5], [9]))AS Kayit
where  Kayit.[3] is not null
       and kayit.[5] is not null
       and kayit.[9] is not null
)
SELECT *
FROM   cte2
WHERE RowNum <= 25      
order  by movie_id

Upvotes: 3

Related Questions