What SQL statement for top 5 postcounts?

Snitz™ Forums 2000
https://forum.snitz.com/forumTopic/Posts/68739?pagenum=1
06 November 2025, 08:19

Topic

kyodai
What SQL statement for top 5 postcounts?
29 June 2009, 18:26


Hi and sorry for disturbing... I am already dangerous enough for basic SQL statements, but now i had the idea to just display like the 5 people that have the highest post count like "Top five posters:".
How would the appropriate SQL staement look like? Is there something like "GET * from users.postcount WHERE 5 highest" or so? I am sure there is some way. Bonus points if you can tell me then how to get the 5 highest post counts of today :)

 

Replies ...

HuwR
29 June 2009, 18:30


depends on the database type you are using as they hvae slightly different syntax, but for Microsoft SQL it would be
something like SELECT TOP 5 M_NAME,M_POSTS FROM FORUM_MEMBERS ORDER BY M_POSTS DESC
kyodai
29 June 2009, 18:34


Ah thanks, you're the best. I guess the "5 top posters of today" would be more complicated? Or could i just add like "FROM POSTS WHERE POSTS.POSTDATE = TODAY" ? well or similar to that. Oh and thanks a lot again! Really made my day. :)
Etymon
29 June 2009, 19:42


Originally posted by kyodai
Ah thanks, you're the best. I guess the "5 top posters of today" would be more complicated? Or could i just add like "FROM POSTS WHERE POSTS.POSTDATE = TODAY" ? well or similar to that. Oh and thanks a lot again! Really made my day. :)

That would be:

"SELECT TOP 5 M_NAME,M_POSTS WHERE T_DATE = " & DateToStr(strForumTimeAdjust) & " AND T_STATUS = 1 FROM FORUM_MEMBERS ORDER BY M_POSTS DESC"

In the above, you are checking for posts that are not held (thus hidden) by moderation by looking for the T_STATUS = 1 values. You are checking for posts that are relative to right now while also including your hour offset that may have determined in your admin section under Server Date/Time Configuration. You are checking against strForumTimeAdjust since my today and your today could be two different days.
Remember that if your strTablePrefix variable has a value set to something other than FORUM_ then FORUM_MEMBERS will be different. FORUM_ is the default out-of-the-box value in a fresh Snitz install.
I am pretty sure all of that is correct. Someone will correct me if not.
SiSL
30 June 2009, 07:07


Will do Etymon :) Where is FORUM_TOPICS link in that SQL? :p For last 24 hours, I'd use DateToStr(Now-1)

SELECT TOP 5 M.M_NAME, (SELECT COUNT(1) AS Count1 FROM FORUM_TOPICS T WHERE T.T_AUTHOR = M.M_NAME AND T.T_STATUS = 1 AND T.T_DATE > '" & DateToStr(Now-1) & "') AS Topic_Count, (SELECT COUNT(1) AS Count2 FROM FORUM_REPLY R WHERE R.R_AUTHOR = M.M_NAME AND R.R_STATUS = 1 AND R.R_DATE > '" & DateToStr(Now-1) & "') AS Reply_Count FROM FORUM_MEMBERS ORDER BY Topic_Count DESC

Can be for instance :)
kyodai
30 June 2009, 12:57


Thanks a lot both of you. But now i have a problem to get the names out of the record set. I tried the following pathetic try, but it obviously doesnt work like this:

Code:


	'## Forum_SQL to grab top 5 posters
	strSql = "SELECT TOP 5 M_NAME,M_POSTS FROM FORUM_MEMBERS ORDER BY M_POSTS DESC;"
	

	Set rstop = Server.CreateObject("ADODB.Recordset")
	rstop.open strSql, my_Conn, adOpenForwardOnly, adLockReadOnly, adCmdText

	if rstop.EOF then
		'top5user not available
	else
		top5user1 = rstop.GetRows(0)
		top5user2 = rstop.GetRows(1)
		top5user3 = rstop.GetRows(2)
		top5user4 = rstop.GetRows(3)
		top5user5 = rstop.GetRows(4)
		response.write "<br>Top 5 Posters: " & top5user1 & "," & top5user2 & "," & top5user3 & "," & top5user4 & "," & top5user5 & "."
	end if

	rstop.close
	set rstop = nothing
SiSL
30 June 2009, 15:09


GetRows(x), takes X number of records and returns an array, not a result...
if you say:

top5users = rstop.GetRows(5)

Then :

top5user1 = top5users(0,0)
top5user2 = top5users(0,1)
top5user3 = top5users(0,2)
top5user4 = top5users(0,3)
top5user5 = top5users(0,5)

Carefree
30 June 2009, 15:13


Code:

strSql="SELECT TOP 5 M_POSTS, M_NAME FROM " & strMemberTablePrefix & "MEMBERS ORDER BY M_POSTS DESC"
set rsTop5=my_Conn.Execute(strSql)
Response.Write	"Top 5 Posters: "
if not rsTop5.EOF then
	xx=0
	rsTop5.MoveFirst
	do until rsTop5.EOF
		Response.Write	rsTop5("M_NAME")
		xx=xx+1
		if xx<5 then
			Response.Write	", "
		else
			Response.Write	"."
		end if
		rsTop5.MoveNext
	Loop
	rsTop5.Close
end if
set rsTop5=Nothing
kyodai
30 June 2009, 16:18


OK at first thanks to all of you. I really thought recordset was an array, but okay now its obvious.
Carefree i used your code, works like a charm. Now i am trying to do the "Top 5 today" and Sisls code looked promising (last 24 hours does make sense), so i tried to implement it like this:


Code:


strSql="SELECT TOP 5 M_NAME, (SELECT COUNT(1) AS Count1 FROM FORUM_TOPICS T WHERE T.T_AUTHOR = M_NAME AND T.T_STATUS = 1 AND T.T_DATE > '" & DateToStr(Now-1) & "') AS Topic_Count, (SELECT COUNT(1) AS Count2 FROM FORUM_REPLY R WHERE R.R_AUTHOR = M_NAME AND R.R_STATUS = 1 AND R.R_DATE > '" & DateToStr(Now-1) & "') AS Reply_Count FROM FORUM_MEMBERS ORDER BY Topic_Count DESC"
set rsTop5=my_Conn.Execute(strSql)
Response.Write	"<br>Today's most active Members: "
if not rsTop5.EOF then
	xx=0
	rsTop5.MoveFirst
	do until rsTop5.EOF
		Response.Write	rsTop5("M_NAME")
		xx=xx+1
		if xx<5 then
			Response.Write	", "
		else
			Response.Write	"."
		end if
		rsTop5.MoveNext
	Loop
	rsTop5.Close
end if
set rsTop5=Nothing


Okay apart from changing M.M_NAME to just M_NAME (it didnt like the M. somehow) i get an error saying "Conversion failed when converting the nvarchar value 'fronzel' to data type int. " Well i understand so much that it is trying to convert my username (fronzel) to an integer. I think it happens here: "AS Reply_Count" i think i can not get the result as Reply_Count as its no number but a string. How do i need to define it?

Edit: Okay i think the Reply_Count is not the problem as i replaced it with nvarchar and still get the error. So somewhere i seem to convert my username into an integer but i cant understand where. Any ideas?

Edit 2: Also tried the solution by Etymon - but it always gets me back "Incorrect syntax near the keyword 'FROM'. " - although i really cant see anything wrong here. Checked my tables and its definitely FORUM_MEMBERS, dunno what it wants from me.
© 2000-2021 Snitz™ Communications