[RESOLVED] ROW_NUMBER() OVER with derived column

Hi, I'm trying to set up paging on a sql server 2005 db by using row_number() over using a derived column, I'm getting an error saying that the derived column doesn't exist can anyone see a way around his or perhaps a better way of doing it? I've hilighted the offending position in yellow

Code:

---

declare @Jnl_Code varchar(10)
declare @Id numeric
declare @startRowIndex int
declare @maximumRows int
set @startRowIndex=0
set @maximumRows=20
set @Jnl_Code='bj'
set @Id=1

DECLARE @Rec_Cnt int
DECLARE @More_Recs int

IF @startRowIndex Is Null OR @startRowIndex < 0 BEGIN SET @startRowIndex=0 END
IF @maximumRows Is Null OR @maximumRows < 1 BEGIN SET @maximumRows=20 END

SET @Rec_Cnt=(SELECT Count(v.MS_No) FROM MS_Version v WHERE (v.Submit_Auth_ID=@ID OR (SELECT COUNT(Author_Id) FROM Authors a WHERE a.MS_No=v.MS_No AND a.Version_No=v.Version_No AND a.User_ID=@ID AND a.Deleted=0)>0))
SET @More_Recs=(@Rec_Cnt -((@startRowIndex+@maximumRows)-1))

IF @More_Recs < 0 BEGIN SET @More_Recs=0 END

SET @startRowIndex = @startRowIndex + 1
SELECT s.MS_No
        ,s.Version_No
        ,s.Submit_Date
        ,s.Received_Date
        ,s.Title
        ,s.Submit_Auth_Id
        ,s.Sub_Status
        ,s.Status
        ,s.Decision_Date
        ,s.US_Paper
        ,s.JnlCodeSortOrder
        ,MoreRecords=(@More_Recs)
        ,RecCount=(@Rec_Cnt)
        ,s.RowNum
FROM
        (SELECT v.MS_No
                ,v.Version_No
                ,v.Submit_Date
                ,v.Received_Date
                ,v.Title
                ,v.Submit_Auth_Id
                ,v.Sub_Status
                ,v.Status
                ,v.Decision_Date
                ,v.US_Paper
                ,CASE v.Jnl_Code
                        WHEN @Jnl_Code THEN 0
                        ELSE 1
                  END As JnlCodeSortOrder
                ,ROW_NUMBER() OVER(ORDER BY JnlCodeSortOrder ASC, v.Jnl_Code ASC, v.MS_No DESC, v.Version_No DESC) As RowNum
        FROM MS_Version v
        WHERE (v.Submit_Auth_ID=@ID
        OR (SELECT COUNT(Author_Id) FROM Authors a WHERE a.MS_No=v.MS_No AND a.Version_No=v.Version_No AND a.User_ID=@ID AND a.Deleted=0)>0)
        ) As s
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1

---

Any help will be greatly appreciated

Cheers Al

You must put the CASE statement in the OVER() clause, not the alias name.

ROW_NUMBER() OVER(ORDER BY CASE v.Jnl_Code WHEN @Jnl_Code THEN 0 ELSE 1 END.....)

kaffenils,

Thanks very much, that did the trick

Cheers Al