[RESOLVED] ROW_NUMBER() OVER with derived column

[aconybeare]

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

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

[kaffenils]

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.....)

[aconybeare]

kaffenils,

Thanks very much, that did the trick

Cheers Al