Selecting and ordering correctly..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Selecting and ordering correctly..

Author

Topic

TazMania
Yak Posting Veteran

63 Posts

Posted - 2003-10-30 : 09:07:33

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

1456 Posts

Posted - 2003-10-30 : 15:20:03

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.

TazMania
Yak Posting Veteran

63 Posts

Posted - 2003-10-30 : 16:19:13

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

TazMania
Yak Posting Veteran

63 Posts

Posted - 2003-10-31 : 05:01:17

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources