SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Selecting and ordering correctly..
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

TazMania
Yak Posting Veteran

Denmark
63 Posts

Posted - 10/30/2003 :  09:07:33  Show Profile  Reply with Quote
Hi

I'm currently in the middle of coding my own forum in asp..
But I've ran into this dilemma.

In my sqldb i've got a column named sticky of the type bit and two columns named threaddate and topicdate.
What I looking for is.

When the sticky bit is 1 it will always be on top and when there's a new topic or thread it will also be ordered by threaddate or topicdate.

Which i've archived this way :
SQL = " SELECT forumPublicTopic.topicID,forumPublicTopic.katHovedTitelTal,
forumPublicTopic.katID,forumPublicTopic.itemDATE,forumPublicTopic.itemVIEWS,
forumPublicTopic.itemCOUNT,forumPublicTopic.topicICON,
forumPublicTopic.topicSTICK,forumPublicTopic.topicDATE,
forumPublicTopic.topicUSER,
forumPublicTopic.topicSUBJECT,forumPublicTopic.topicBODY,forumPublicTopic.topicLOCK,forumPublicTopic.topicLOCKusrid,
Members.ID,Members.Username FROM ForumPublicTopic LEFT OUTER JOIN Members ON ForumPublicTopic.topicUSER=Members.ID WHERE katID='"&Request.QueryString("forumID")&"' ORDER BY itemDATE,topicDATE DESC"
Set RS = Conn.Execute(SQL)


But would you suggest to add and outer where clause saying sticky='0' and then loop through this sql string and then code another sql string where sticky='1' and again loop through this string, or is there another way that i'm not aware of ?

Best regards
Taz

mohdowais
Sheikh of Yak Knowledge

United Arab Emirates
1456 Posts

Posted - 10/30/2003 :  15:20:03  Show Profile  Visit mohdowais's Homepage  Reply with Quote
How about UNION?

SELECT Col1, Col2 FROM Forums WHERE Sticky = 1
UNION
SELECT Col1, Col2 FROM Forums WHERE Sticky = 0 ORDER BY DatePosted DESC

But why are you re-inventing the wheel? Plenty of forums available that will run straight out of the box...the one SQLTeam uses (Snitz) is fantastic, and best of all, free!

Owais


Where there's a will, I want to be in it.
Go to Top of Page

TazMania
Yak Posting Veteran

Denmark
63 Posts

Posted - 10/30/2003 :  16:19:13  Show Profile  Reply with Quote
Hi mohdowais,

Thx for your reply much apriciated.
The reason for me to developing my own forum is first of all that i'm learning alot from it and secondly i know what is going on in the code..

Best regards
Taz
Go to Top of Page

TazMania
Yak Posting Veteran

Denmark
63 Posts

Posted - 10/31/2003 :  05:01:17  Show Profile  Reply with Quote
Hi again,

Finally it worked.. apparently union does only support one order by statement so I changed my where Clause and my order by to :

WHERE katID='"&Request.QueryString("forumID")&"' ORDER BY topicSTICK DESC,topicDATE DESC"

Best regards
Taz
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000