0

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

2
Contributors
2
Replies
3
Views
8 Years
Discussion Span
Last Post by mattpointblank
0

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

Edited by Reverend Jim: Fixed formatting

0

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.