Processing Queues represented by tables

[azwaan]

I have a table structure that represents a queue, like so..

ItemNo PrecededBy
1 NULL
3 1
4 3
5 6
6 4

can anyone suggest any efficient ways of processing such a structure? for example if i need to find the last item in the queue etc..

im using SQL 2005..

 

[bboffin]

To get the first item use:
SELECT TOP 1 * FROM QTable ORDER BY ItemID

To get the last item use:

SELECT TOP 1 * FROM QTable ORDER BY ItemID DESC


Bob Boffin

 

[azwaan]

thats not quite right.. if u see closely the order of the items is determined by the PrecededBy field. so its actually ItemNo - 5 that is the last item in the queue. while your query would return ItemID 6.

 

[gmmastros]

Select *
From   TableName
Where  PrecededBy = (Select Max(PrecededBy) From TableName)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[azwaan]

still not quite right... be mindful that these items can be moved around.. (eh..well not quite a queue.. more like a linked list then)... consider this

ItemNo PrecededBy
1 NULL
3 1
4 5
5 8
6 3
7 6
8 7

now its itemID 4 that is the last item.. and finding the last item is not the only problem.. what if i need to order these items.. can anyone suggest the best approach in processing such a list with the maximum efficiency?

 

[SQLSister]

I would change the structure. It makes no sense. If I had a list of items that were, say, the steps in a process. I would have an OrderBy column. It would go from 1 to whatever the number of steps were. I would then have an instead of trigger that would adjust the order if one of the records was changed. So if I moved Item 6 from being the 8th step to the third step, it would first set item 6 to zero, then adjust every item from 3-7 up one and then make item 6 the third one. I did this for one job a long time ago and it worked well. That way if you have multiple processes with different step orders you can always order them simply by using order by Process, OrderBy in the select. And selecting one process would mean you would just have to use the OrderBY column in the order by clause (with a where clasue to select the process you wanted). The way you are storing data would you could use a cursor to figure out the current order and those are slow and should be avoided. I'd like to thinkthere is a non-cursor based solution to your problem but can't thinkof one offhand. Truly this is design error. It needs to be fixed if you ever expect to get performance out of this system.

"NOTHING is more important in a database than integrity." ESquared

 

[azwaan]

thanks for your valuable thoughts.. an alternative to cursors i found out, is using Recursive CTE (common table expressions)

it makes for cleaner code. , but i suppose large tables would make this inefficient as well.. but for small sets it serves the purpose.

 

[ca8msm]

I did something similar a while ago with a CTE. Maybe that can help you.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]