Order by random - within a field?

[mattpointblank]

Hi everyone.

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?

Thanks,
Matt

[dickersonka]

how is this random?

• 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

you need to pick one or the other, sorting with type and section or not

i don't see how you can have both

[mattpointblank]

No worries, I did it server side instead. As I was looping through the results I tested for the various attributes and sorted them into separate chunks of HTML depending on what their statuses were - it works fine. Thanks anyway.