Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
 All Forums
 Help Groups for Snitz Forums 2000 Users
 Help: General / Classic ASP versions(v3.4.XX)
 Having some trouble with ROW_NUMBER()

Note: You must be registered in order to post a reply.
To register, click here. Registration is FREE!
Before posting, make sure you have read this topic!

Screensize:
UserName:
Password:
Format Mode:
Format: BoldItalicizedUnderlineStrikethrough Align LeftCenteredAlign Right Horizontal Rule Insert HyperlinkInsert EmailInsert Image Insert CodeInsert QuoteInsert List
   
Message:

* HTML is OFF
* Forum Code is ON
Smilies
Smile [:)] Big Smile [:D] Cool [8D] Blush [:I]
Tongue [:P] Evil [):] Wink [;)] Clown [:o)]
Black Eye [B)] Eight Ball [8] Frown [:(] Shy [8)]
Shocked [:0] Angry [:(!] Dead [xx(] Sleepy [|)]
Kisses [:X] Approve [^] Disapprove [V] Question [?]

 
   

T O P I C    R E V I E W
bobby131313 Posted - 15 March 2020 : 22:54:02
I am on SQL Server 2008 so offset doesn't work. I want to get the last 15 replies to a topic.

	ReplyCountOffset = (Topic_Replies - strPageSize)
'	Response.Write ReplyCountOffset
	'## Forum_SQL
	strSql = "SELECT ROW_NUMBER() OVER(ORDER BY R.DATE) AS ROW, M.M_NAME, M.M_RECEIVE_EMAIL, M.M_AIM, M.M_ICQ, M.M_MSN, M.M_YAHOO, M.M_STATUS"
	strSql = strSql & ", M.M_TITLE, M.MEMBER_ID, M.M_HOMEPAGE, M.M_LEVEL, M.M_POSTS, M.M_COUNTRY, M.M_AVATAR_URL, M_SUPPORTER, M_MOD_NOTE"
	strSql = strSql & ", R.REPLY_ID, R.FORUM_ID, R.R_AUTHOR, R.TOPIC_ID, R.R_MESSAGE, R.R_LAST_EDIT"
	strSql = strSql & ", R.R_LAST_EDITBY, R.R_SIG, R.R_STATUS, R.R_DATE, R.R_MOD_NOTE"
	if CanShowSignature = 1 then
		strSql = strSql & ", M.M_SIG"
	end if
	strSql2 = " FROM " & strMemberTablePrefix & "MEMBERS M, " & strActivePrefix & "REPLY R "
	strSql3 = " WHERE M.MEMBER_ID = R.R_AUTHOR AND ROW > " & ReplyCountOffset
	strSql3 = strSql3 & " AND R.TOPIC_ID = " & Topic_ID & " "
		' DEM --> if not a Moderator, all unapproved posts should not be viewed.
		if AdminAllowed = 0 then
			strSql3 = strSql3 & " AND (R.R_STATUS < "
			if Moderation = "Y" then
				' Ignore unapproved/rejected posts
				strSql3 = strSql3 & "2"
			else
				' Ignore any previously rejected topic
				strSql3 = strSql3 & "3"
			end if
		strSql3 = strSql3 & " OR R.R_AUTHOR = " & MemberID & ")"
	end if


I keep getting this error....

Microsoft SQL Server Native Client 10.0 error '80040e14'
Invalid column name 'ROW'.

Everything I've read seems to say it should work. Any ideas?
2   L A T E S T    R E P L I E S    (Newest First)
bobby131313 Posted - 17 March 2020 : 16:22:02
I'll give it try, thank you!
HuwR Posted - 17 March 2020 : 08:17:39
Your problem is you can't use an aliased column name in a where clause, so you can't do "WHERE M.MEMBER_ID = R.R_AUTHOR AND ROW > " & ReplyCountOffset
you SQL needs to follow this pattern (it should work in 2008)


WITH MemberReplies AS  
(  
    SELECT ROW_NUMBER() OVER (ORDER BY R.DATE) AS RowNumber  …. Other fields here
    FROM FORUM_MEMBERS M JOIN  FORUM_REPLY R ON R.R_AUTHOR = M.MEMBER_ID
)   
SELECT *    
FROM MemberReplies    
WHERE RowNumber > ReplyCountOffset

Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000 Version 3.4.07