don - thanks for looking into my problem.
first of all, everything in the case statement is obviously operating
on any row it queries and returns back to me - which seems
logical enough to me. it seems to be working, in that regard - i
get one row for each content_item_id, and the distance and map
values are correct. so the query is working fine, as it is, except
that the rows are only ordered by distance, map, or release date
within a given content_item_id. (i realize that i had two
content_item_id's in the order by clause, and i took the last one
out - it worked both ways, of course). so i'm getting the RIGHT
rows, just not in the right overall order.
what i'm trying to do is this: i'm displaying a listing of content
items to the user. i know what category he's in, so i want to
display items of that category to him, and secondarily items of
the subcategories, if there aren't enough in the category itself. i
need to display them in order of their importance in the given
category, which is optionally bolstered by a "push" that we enact
on certain important items. items of the same mapping_weight
should be displayed in reverse chronological order, so if all
items are the same weight, the newest ones come out on top.
so far, i've been advised to encapsulate the entire query in the
from of another clause, which would be slow but it would work -
except that i'm using postgresql.
any other advice?
