[thesite] [bug] t.e.o. - Other Articles Written by this Author
rudy
r937 at interlog.com
Fri Nov 16 15:07:20 CST 2001
> 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
More information about the thesite
mailing list