How to insert a row between two rows and give it priority in the database?

Question

I have a message column in a database table. I want to send these messages by priority, so I added the Priority column to the Messages table.

but what if I want to insert a "cram" message between two messages and give a previous priority to this new message ?.

Do I have to update all message priorities in this message.

So please give me the perfect design for my database table to support priority updates.

Answer 1

float , int.

, , Priority. (, cram 2 3, 2.5).

, / .. , ( , ). ​​

ORDER BY. "ShowAfter" 0. cram, , , , [ShowAfter] 1.

Answer 2

, , , . 3

, , , , . , . .

, 2 ( ).

Answer 3

getdate(). , order by priority asc, createtime desc.

Last-In-First-Out (LIFO), order by priority, senddate, senddate 1/1/1900 , .

, , "" . getdate() .

Answer 4

, , , , ?

, , / .

?

Answer 5

@Jimmy Chandra, .

, :

ID | SortAfterID | OtherColumn1 | OtherColumn2

, 1 5, , 5 2 3. :

ID | SortAfterID | OtherColumn1 | OtherColumn2
1  | NULL        | ...          | ...
2  | 1           | ...          | ...
3  | 5           | ...          | ...
4  | 3           | ...          | ...
5  | 2           | ...          | ...

, SortAfterID ID.

(ID = 6), 1 2, :

• = 6 SortAfterID = 1.
• = 2, SortAfterID = 6.

, , , . by @richardtallent , , , .

@richardtallent, , , , , .