07. oktober 2004 - 09:51Der er
14 kommentarer og 1 løsning
List 5 poster fra db
Hey!!!!
Har tidligere fået lavet dette script.
KODE: <% Set con= Server.CreateObject("ADODB.connection") Provider = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("forum/admin/database/wwForum.mdb") con.Open Provider
SqlStr = "SELECT tblForum.hide, * FROM tblForum RIGHT JOIN tblTopic ON tblForum.forum_id = tblTopic.forum_ID WHERE (((tblTopic.Topic_ID) In (Select top 5 Topic_ID From tblThread order by thread_ID DESC)) AND ((tblForum.hide)=False))order by tblTopic.Last_entry_date DESC;" Set rs = con.Execute(SqlStr)
rs.MoveFirst do while not rs.eof forumdate = rs("Last_entry_date") If DateDiff("d", date(), rs("Last_entry_date")) = 0 Then strStyle = "font-weight: bold;" Else strStyle = "" End If Response.Write("<span style=""" & strStyle & """>" & left(right(rs("Last_entry_date"),8),5) & "</span>") %> <br> <a href="forum/forum_posts.asp?TID=<%=rs("Topic_ID")%>" target="_blank"><%=rs("subject")%></a><br><br> <% rs.movenext loop %>
Scriptet virker fint, ingen fejl - men desværre ikke helt efter hensigten.
Problemet er at den henter de 5 nyeste indlæg - Men det kan sagtens være under samme spørgsmål.
Så derfor skrifter listen hele tiden mellem at den lister 5 poster og at den lister 1.
Kan I hjælpe, så den springer posten over, hvis den allerede er nævnt, eller noget i den stil :o)
I dette særtema ser vi på, hvordan cloud og AI bliver fundamentet for virksomhedernes digitale forretning, og hvordan de nye muligheder for automatisering og forretningsværdi kan udnyttes uden at miste overblik, sikkerhed og menneskelig kontrol.
SqlStr = "SELECT tblForum.hide, * FROM tblForum RIGHT JOIN tblTopic ON tblForum.forum_id = tblTopic.forum_ID WHERE (((tblTopic.Topic_ID) In (Select DISTINCT top 5 Topic_ID From tblThread order by thread_ID DESC)) AND ((tblForum.hide)=False))order by tblTopic.Last_entry_date DESC;"
ORDER BY clause (thread_ID) conflicts with DISTINCT.
site.asp, line 10
SqlStr = "SELECT tblForum.hide, * FROM tblForum RIGHT JOIN tblTopic ON tblForum.forum_id = tblTopic.forum_ID WHERE (((tblTopic.Topic_ID) In (Select distinct top 5 Topic_ID From tblThread order by thread_ID DESC)) AND ((tblForum.hide)=False))order by tblTopic.Last_entry_date DESC;" Set rs = con.Execute(SqlStr)
SqlStr = "SELECT tblForum.hide, * FROM tblForum RIGHT JOIN tblTopic ON tblForum.forum_id = tblTopic.forum_ID WHERE (((tblTopic.Topic_ID) In (Select (DISTINCT top 5) Topic_ID From tblThread order by thread_ID DESC)) AND ((tblForum.hide)=False))order by tblTopic.Last_entry_date DESC;"
Syntax error. in query expression '(((tblTopic.Topic_ID) In (Select (DISTINCT top 5) Topic_ID From tblThread order by thread_ID DESC)) AND ((tblForum.hide)=False))'.
du må lave dette: Select distinct top 5 Topic_ID From tblThread order by thread_ID DESC
sådan her:
select top 5 topic_id from (SELECT max(thread_id) as tmpid, tblThread.Topic_ID FROM tblThread GROUP BY tblThread.Topic_Id) order by tmpid desc
Så din SQL sætning bliver:
SqlStr = "SELECT tblForum.hide, * FROM tblForum RIGHT JOIN tblTopic ON tblForum.forum_id = tblTopic.forum_ID WHERE (((tblTopic.Topic_ID) In (select top 5 topic_id from (SELECT max(thread_id) as tmpid, tblThread.Topic_ID FROM tblThread GROUP BY tblThread.Topic_Id) order by tmpid desc)) AND ((tblForum.hide)=False))order by tblTopic.Last_entry_date DESC;"
den kan laves om så det er en SQL som også tager top 5: select topic_id from (SELECT top 5 max(thread_id) as tmpid, tblThread.Topic_ID FROM tblThread GROUP BY tblThread.Topic_Id order by max(thread_id) desc)
SqlStr = "SELECT tblForum.hide, * FROM tblForum RIGHT JOIN tblTopic ON tblForum.forum_id = tblTopic.forum_ID WHERE (((tblTopic.Topic_ID) In (select topic_id from (SELECT top 5 max(thread_id) as tmpid, tblThread.Topic_ID FROM tblThread GROUP BY tblThread.Topic_Id order by max(thread_id) desc))) AND ((tblForum.hide)=False))order by tblTopic.Last_entry_date DESC;"
Lige som før du skal bare copy/paste SQL sætningen SqlStr. Så det bliver:
SqlStr = "SELECT tblForum.hide, * FROM tblForum RIGHT JOIN tblTopic ON tblForum.forum_id = tblTopic.forum_ID WHERE (((tblTopic.Topic_ID) In (SELECT top 5 Topic_ID FROM tblThread GROUP BY Topic_Id order by max(thread_id) desc)) AND ((tblForum.hide)=False))order by tblTopic.Last_entry_date DESC;" Set rs = con.Execute(SqlStr)
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.