I'm printing a list of articles from my database. Some articles are set as 'sponsored', meaning they appear at the top of the list. Articles also have a 'type' field - if it's set to 0 then there is no specific type, otherwise the value corresponds to a regular article category. As well as this, we also have a 'section' field which I also want to order by - values with a low section ID should appear at the bottom.
What I want to do is order my result set so that:
- sponsored articles appear first
- the rest of the results should show in a random order:
- articles with higher values of 'type' appear first
- then all the articles with 'type=0' should appear at the end
- then all the articles with a low section ID should appear
My query looks like this:
ORDER BY sponsored DESC, newid(), type DESC, section DESC
This doesn't do what I want it to - the random function breaks the ordering and if I move it further in the chain it doesn't have any effect. Is it possible to do what I explained above, like by ordering by a specific field randomly, rather than the entire table?