[thesite] [bug] t.e.o. - Other Articles Written by this Author

[rudy]

> how to take this query, which keeps articles in certain categories
> from being returned:....
>
> [ le grand sql snippage ]
>
> ... and change it so it will return articles in all categories,
> but ordered such that articles with a categoryid of 1, 23, 26,
> or 9741 appear last in the results?

piece of cake with a UNION ALL

   select articles
         where category NOT IN (special)
 union all
   select articles
         where category IN (special)

now the question is how to sort them, and below i have introduced a special
sort column called foo

  SELECT 1 as foo
       , content.contentid
       , content.contentname
       , content.synopsis
       , content.ratings
       , content.rating
       , content.replies
       , content.categoryid
       , content.datemod
       , (SELECT category
            FROM categorys
           WHERE categorys.categoryid = content.categoryid
         ) AS category
    FROM content
   WHERE content.userid = #Val(getarticle.userid)#
     AND content.categoryid NOT IN (1, 23, 26, 9741)
     AND content.contentid <> #Val(url.aid)#
     AND content.signoff = 1
 UNION ALL
  SELECT 2
       , content.contentid
       , content.contentname
       , content.synopsis
       , content.ratings
       , content.rating
       , content.replies
       , content.categoryid
       , content.datemod
       , (SELECT category
            FROM categorys
           WHERE categorys.categoryid = content.categoryid
         )
    FROM content
   WHERE content.userid = #Val(getarticle.userid)#
     AND content.categoryid  IN (1, 23, 26, 9741)
     AND content.contentid <> #Val(url.aid)#
     AND content.signoff = 1
   ORDER BY foo ASC
       , content.rating DESC
       , content.ratings DESC
       , content.datemod ASC

thus the articles in normal categories come ahead of all articles in
special categories

of course, other sort sequences can be devised...

note the ORDER BY applies to the combined result set, not just the second
subselect as it might appear from a cursory glance at the syntax (common
sql newbie misunderstanding)

also note i don't use column aliases in the 2nd subselect, this is standard
sql coding practice from long ago, not sure if it's still de rigeuer, but i
believe the aliases from the first subselect always apply...

rudy