I have a problem with mysql query. It's little bit hard to describe the problem, but i have to give a try. I have simple table with blog posts:

posts:
id
text
time
sticky

Posts should be ordered by time desc and that's ok. But, my sticky column is not regular sticky column from other blogs and forums. I mean, i have more sticky posts and i don't want them all to be stick to the top of the list.

I have this structure of content: 6 normal posts and then one sticky, one per day in the week. On the index page are listed posts from last 3 weeks. What i want is to sort them in this order:

s p p p p p p s p p p p p p s p p p p p p

In every group of 7 posts there is one sticky and it should be fixed to the top of that group, but not all three sticky posts to be fixed at the top. So, it's basically 3 separate lists with 7 elements and every list have one sticky post at the top.

I'm not sure if i should do this with table union, or group by or combination, i'm not sure. If anyone has any idea, i'd really appreciate if he could share it.

Thanks in advance.
Milan

Neither union nor group by will help you here.
As far as I know, you cannot order in a sequential way like this, directly from a query.

Your best bet would be to process this not in a db, but in a server side language.
Having an array of normal posts, an array of sticky posts, and inserting a sticky between 2 regular during a loop over the regular posts.

You could do that in the db, by using 2 cursors too.
But, as the mysql documentation state, cursor are only allowed into stored procedure or functions.
http://dev.mysql.com/doc/refman/5.0/en/cursors.html

So, if you really really want to do that in the db, you'll have to create an stored procedure and call it rather than sending a complete query.

The logic of the SP would be (in my opinion):

set regular_cnt = 0
set sticky_cnt=0
open cursor_regular on regular posts
open cursor_sticky on sticky posts
while not end of cursor_regular:
    if modulo(regular_cnt,6)==0 and regular_cnt>0:  --not the first regular, but multiple of 7
        fetch next sticky from cursor_sticky
        set sticky_cnt+=1
    fetch next regular from cursor_regular
    set regular_cnt+=1
end while