What SQL statement for top 5 postcounts?

The Forum has been Updated

The code has been upgraded to the latest .NET core version. Please check instructions in the Community Announcements about migrating your account.

What SQL statement for top 5 postcounts? - Posted 29 Jun 2009 18:26 (1303 Views)

kyodai

New Member

Posts: 74

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

Posted 29 Jun 2009 18:30

HuwR

Forum Admin

Posts: 20611

20611

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

Posted 29 Jun 2009 18:34

kyodai

New Member

Posts: 74

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

Posted 29 Jun 2009 19:42

Etymon

Advanced Member

Posts: 2396

2396

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.

Posted 30 Jun 2009 07:07

SiSL

Average Member

Posts: 671

671

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

Posted 30 Jun 2009 12:57

kyodai

New Member

Posts: 74

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

Posted 30 Jun 2009 16:18

kyodai

New Member

Posts: 74

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.

Last edited by kyodai on 30 June 2009, 16:34