Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Snitz Forums 2000 MOD-Group
 MOD Add-On Forum (W/O Code)
 What SQL statement for top 5 postcounts?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kyodai
New Member

Azerbaijan
74 Posts
Posted - 29 June 2009 :  18:26:54  Show Profile  Reply with Quote
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 :)

HuwR
Forum Admin

United Kingdom
20584 Posts
Posted - 29 June 2009 :  18:30:24  Show Profile  Visit HuwR's Homepage  Reply with Quote
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
Go to Top of Page

kyodai
New Member

Azerbaijan
74 Posts
Posted - 29 June 2009 :  18:34:50  Show Profile  Reply with Quote
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. :)
Go to Top of Page

Etymon
Advanced Member

United States
2385 Posts
Posted - 29 June 2009 :  19:42:10  Show Profile  Visit Etymon's Homepage  Reply with Quote
quote:
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.
Go to Top of Page

SiSL
Average Member

Turkey
671 Posts
Posted - 30 June 2009 :  07:07:11  Show Profile  Visit SiSL's Homepage  Reply with Quote
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 :)
CHIP Online Forum

My Mods
Select All Code | Fix a vulnerability for your private messages | Avatar Categories W/ Avatar Gallery Mod | Complaint Manager
Admin Level Revisited | Merge Forums | No More Nested Quotes Mod
Go to Top of Page

kyodai
New Member

Azerbaijan
74 Posts
Posted - 30 June 2009 :  12:57:53  Show Profile  Reply with Quote
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:



	'## 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
Go to Top of Page

SiSL
Average Member

Turkey
671 Posts
Posted - 30 June 2009 :  15:09:10  Show Profile  Visit SiSL's Homepage  Reply with Quote
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)
CHIP Online Forum

My Mods
Select All Code | Fix a vulnerability for your private messages | Avatar Categories W/ Avatar Gallery Mod | Complaint Manager
Admin Level Revisited | Merge Forums | No More Nested Quotes Mod
Go to Top of Page

Carefree
Advanced Member

Philippines
4207 Posts
Posted - 30 June 2009 :  15:13:09  Show Profile  Reply with Quote
quote:


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

Go to Top of Page

kyodai
New Member

Azerbaijan
74 Posts
Posted - 30 June 2009 :  16:18:38  Show Profile  Reply with Quote
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:




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.
Edited by - kyodai on 30 June 2009 16:34:03
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000 Version 3.4.07