A good clean solution(he he)

[thoand]

In my news system (using SQL for data storage)
I need to have a priority for items so the index page can place the item in right the chosen priority,

I usaly do this by using the publish date. (eg.
Pri 1 = 2002.10.11 :08:36:45
Pri 2 = 2002.10.10 :18:48:21
...
)

But this time I can't do that and need a priority nmb (eg 1, 2, 3 ...)

One problem that arises then is:
There would be alot of 1 and 2 and 3 in the priority colum.

One solution could be a query that asks for the latest(by date) with priority 1, so this is placed in on the top.

Another solution is to set priority = 0 where priority = 1 when updating the index(so ther will always be one row in the table with 1).

Does anyone have better cleaner solution to this?

[herve]

Hi,

One problem that arises then is:
There would be alot of 1 and 2 and 3 in the priority colum.

Maybe I have missed something, but for me there's only
one priority level for a given news (?)

One solution could be a query that asks for the latest(by date)
with priority 1, so this is placed in on the top.

Yes. Using a "ORDER BY publish_date, priority" statement for instance. Ordering first one the publish date, then on the priority
order.

Another solution is to set priority = 0 where priority = 1 when
updating the index(so ther will always be one row in the table
with 1).

I don't know how your priority level is managed, but if
on Monday you have :

News 1 Priority Level 3
News 2 Priority Level 2
News 3 Priority Level 1

at midnight if you UPDATE with "set priority = 0 where priority = 1"
then you will have :

News 1 Priority Level 3
News 2 Priority Level 2
News 3 Priority Level 0

... which may not be what you really want 🙂

If the news are "fadding away" what you need is to decrease
each level like that :

"set priority = priority -1 where priority > 0"

so that if the first day you have :

News 1 Priority Level 3
News 2 Priority Level 2
News 3 Priority Level 1

the next day you will have :

News 1 Priority Level 2
News 2 Priority Level 1
News 3 Priority Level 0

the next day :

News 1 Priority Level 1
News 2 Priority Level 0
News 3 Priority Level 0

etc.

Hervé.