How to automatically sort SPListItems based on a field

Question

I have a requirement that a SPList should be sorted by the "Priority" field (number field, no limits) when a ListItem is added or updated.

The sort should work as this:

Original        Inserted item         Modified
   1                                      1
   2                2                     2
   3                                      3 (old 2)
                                          4 (old 3)

Edited: This behaviour is primary for improving the user experience when editing data in the data sheet view. It is a sort of a poor mans AJAX style updates that I am trying to get.

The only solution I see right now is to use an event handler and update the ListItems that should be deprioritized, however that could be a fairly costly operation on a list with hundreds of items (unless there are gaps in the sequence).

Have I missed the obvious solution, or a far better scaling way?

tia

Answer 1

If you want the items sorted in a view: Set the sort order in the view.

If you want items sorted when using the SharePoint API: Use a SPQuery with an OrderBy element. http://sharepointmagazine.net/technical/development/writing-caml-queries-for-retrieving-list-items-from-a-sharepoint-list

Answer 2

Wow, tough one. I do not like the idea of going back and doing updates to existing items from a EventReceiver. You are asking for trouble.

Here is my recomendation.

Use 2 fields for your sort.

1 your priorty field as you have it today (so someone says 1, 2 , 3, 4)... 2 the create date field (this is a builtin datetime field).

so your results would look something like this

1 1/1/2009
2 5/16/2009
2 5/11/2009
3 5/12/2009