Page 1 of 1

Priority Listing

Posted: Mon Dec 18, 2006 8:23 am
by iknownothing
Hey all,
Now here is the most pointless excercise I have come across to date.

Here goes, I'm building a task log for internal use and the boss wants to me implement priority listing. Basically when a new task is added, based on current tasks, a particular priority number can be set, anywhere from 1 to a million (not Very High to Very Low, because that would be a normal way to do things...), what it needs to do, is that if a task is already set to 1, and a new task is added which is also 1, the previous task which was allocated 1 would move to 2, and 2 to 3 and so on. In the event of a task deletion the gap left by the deleted task would also adjust to the correct number and follow through, so all tasks will be listed from 1 to whatever it gets to.

So after that big ramble, apologies, my troubles are with the number overwritting and adjusting, I have absolutely no code to show you because I have no idea how to even start it. Can it be done perhaps by somehow giving an ID to each new row created?

Posted: Mon Dec 18, 2006 9:00 am
by feyd
Typically you will have some unique way of addressing the records, so an ID makes sense in a lot of cases.

The general concept is know what record (and priority) is being deleted. You can either delete it before or after an update query that shifts all records greater (or lesser) priority. The update query may look like

Code: Select all

UPDATE tableName SET priority = priority - 1 WHERE priority > 6
Where the record with priority 6 is being removed.

The same sort of query (in reverse) would be performed when wedging in a new record.

Posted: Mon Dec 18, 2006 9:33 am
by iknownothing
cheers, haven't tried it yet, but it looks like a winner, that is 2 times today i have fallen victim to the greater/smaller than operators...