SQL Statment "TOP"
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=14062
Printed Date: 28 March 2026 at 10:49am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: SQL Statment "TOP"
Posted By: Agni
Subject: SQL Statment "TOP"
Date Posted: 01 March 2005 at 4:59pm
|
I am using the following query which should return the latest (top) 12
Topics in my forum. Curently it shows some prepeated Topic_ID values.
(I am using this to add my most recent posts to a XML RSS news feed) Any help would be very welcome and almost certianly be greeted by loads of free beer!!!  Nathan PS I am still using Web Wiz Forums version 6.34 - but I love it! "SELECT TOP 12 dbo.tblThread.Message, dbo.tblThread.Message_date, dbo.tblThread.Topic_ID, dbo.tblThread.Author_ID, dbo.tblAuthor.Username, &n bsp; dbo.tblTopic.Subject, dbo.tblTopic.Forum_ID FROM dbo.tblThread INNER JOIN &n bsp; dbo.tblAuthor ON dbo.tblThread.Author_ID = dbo.tblAuthor.Author_ID INNER JOIN &n bsp; dbo.tblTopic ON dbo.tblThread.Topic_ID = dbo.tblTopic.Topic_ID ORDER BY dbo.tblThread.Message_date DESC" ------------- www.agni.gr Travel To Greece Guide |
Replies:
Posted By: sfd19
Date Posted: 02 March 2005 at 12:18pm
|
Not sure what you mean with "latest topics". The topics where the
latest replies have been made? The latest 12 new topics that have been
created? ------------- Politics, economy & social issues: http://www.studentsfordemocracy.net - StudentsforDemocracy.net |
Posted By: Agni
Date Posted: 02 March 2005 at 1:49pm
|
Correct sorry, I am using this to create an RSS (XML) feed of the most
recent 12 posts main in my forum. Each time a post is made, the code
will be run to update the RSS feed ------------- www.agni.gr Travel To Greece Guide |
Posted By: sfd19
Date Posted: 02 March 2005 at 1:59pm
|
And the result should be the twelve latest post, but only those posts that have been made in different topics? This would be about the same query that is getting used for the 'latest posts' mod for WWF. ------------- Politics, economy & social issues: http://www.studentsfordemocracy.net - StudentsforDemocracy.net |
Posted By: Agni
Date Posted: 02 March 2005 at 2:08pm
|
Yes, but that query does not return the "Author name" or "message". That's why I am trying to join the tables (but unsucessfuly) Cheers for any help you can offer. Nathan ------------- www.agni.gr Travel To Greece Guide |
Posted By: sfd19
Date Posted: 02 March 2005 at 2:19pm
|
I am using these two queries to get the 7 last posts and author names (from 7 different topics): strSQL = "SELECT DISTINCT Top 7 tblTopic.Topic_ID, tblTopic.Subject, tblTopic.Last_entry_date, tblTopic.Forum_ID " strSQL = strSQL & "FROM tblTopic " strSQL = strSQL & "ORDER BY tblTopic.Last_entry_date DESC;" rsCommon.Open strSQL, adoCon Set rsLastAuthor = Server.CreateObject("ADODB.Recordset") Do WHILE NOT rsCommon.EOF strSQL = "SELECT TOP 1 tblThread.Thread_ID, tblThread.Message, tblAuthor.Username " strSQL = strSQL & "FROM tblThread INNER JOIN tblAuthor ON tblThread.Author_ID = tblAuthor.Author_ID " strSQL = strSQL & "WHERE tblThread.Topic_ID = " & rsCommon("Topic_ID") strSQL = strSQL & " ORDER BY tblThread.Thread_ID DESC;" rsLastAuthor.Open strSQL, adoCon ...... I added tblThread.message to the second query so it should fit your needs now. ------------- Politics, economy & social issues: http://www.studentsfordemocracy.net - StudentsforDemocracy.net |