Generic SQL and custom sort orders

[OrangeHopper]

Look I'm trying to do my VAT return so stop asking questions.

I think it all works because the mover of cars knows where things are being moved and has the info about the moved and surrounding items. Therefore a linked list approach only needs a small number of rows to be updated each move. I suspect only three or more likely four updates per move.

You need a unique primary key for by the way.

key, car, next

Now I'm not sure how the sort works. Damn, my head hurts.

Come on, could someone not doing their VAT return please help out.

[DimPrawn]

VAT returns are for accountants. SQL is for real men.

Now, the linked list answer is great and has the wonderful advantage of just a few updates no matter how far an item moves.

HOWEVER, what would the SQL look like to return all the items in the correct order, whether there are 10 or 10,000 items in the table, and would it be fast compared to a sortOrder column?

How would the approach work if there were 1,000,000 items and I just wanted to select those between 100th and 200th place? With the sortOrder column the WHERE clause is simple? With the linked list

Cheers,

DP

[OrangeHopper]

Ok, I conceed defeat.

Doing my VAT return is easier.

Sorry to have wasted yours and my time.

I must learn to shut my mouth.
I must learn to shut my mouth.
I must learn to shut my mouth.
I must learn to shut my mouth.
I must learn to shut my mouth.
I must learn to shut my mouth.
........

[DimPrawn]

No it was very useful.

I'm still awaiting someone to either say "here you go you muppet, this is what a real SQL guru would do, or say, yeah, just go with the sortOrder column and update the rows when an item moves."

Cheers,

DP.

[nobody here but us chicke]

linked lists - do a self join on the table ORacle has a 'connect by prior' feature that eats such things.

for a real implementation I would be asking about how the data would be used and how frequent order changes are compared to queries, then you know how to approach it, perhaps pseudo index table? hold rowid and position in the list or something?

[DimPrawn]

Not using Oracle. Solution needs to be vendor independant (i.e. SQL92 std).

Updates are infrequent and data reads are very high. There will be about 1000 items in the table and they are presented to the user 10 at a time.

[Spacecadet]

one suggestion i would make to speed up the processing during the day is to use large numbers for the ordering

"Ford", 1000
"Vauxhall", 2000
"Honda", 3000
"Toyota", 4000
"Mazda", 5000
"Saab", 6000
"Porsche", 7000
"Mercedes", 8000
"Suzuki", 9000
"BMW", 10000

Then if you move Honda to position 9 you only have to change the honda sort code.

"Ford", 1000
"Vauxhall", 2000
"Toyota", 4000
"Mazda", 5000
"Saab", 6000
"Porsche", 7000
"Mercedes", 8000
"Suzuki", 9000
"Honda", 9500
"BMW", 10000

Then moving Ford to number 8:

"Vauxhall", 2000
"Toyota", 4000
"Mazda", 5000
"Saab", 6000
"Porsche", 7000
"Mercedes", 8000
"Suzuki", 9000
"Ford", 9250
"Honda", 9500
"BMW", 10000

You can then run a batch job overnight to renumber everything so they're clean for the next day. If you've got potential for 10,000,000 items but only a low number of re-ordering updates per day then it might help to speed up immediate processing.

[expat]

I would have thought 4 to 546 move down one place 3 becomes 546 and 547 upwards stay where they are.

Better to avoid these 543 updates if poss. Why not give every record a pointer to successor and one to predecessor? Then when you move 3, you point 2 to 4 and 4 to 2, unhook 546 from 547, and fit 3 in between by pointing it to 546 and 547, and 546 and 547 to 3 (specification of which pointer is pre- and which suc- omitted for clarity).

[DimPrawn]

Okay expat, that's a great suggestion, but I want to do this in SQL, so what would the SQL look like to return the list in the correct order?

The linked list sounds good if all the data is loaded into objects in the middle tier, but I'm looking for a SQL solution for a set of reports. The results need to be retrieved very quickly directly from the database, in the correct order.

Cheers,

DP

[expat]

Okay expat, that's a great suggestion, but I want to do this in SQL, so what would the SQL look like to return the list in the correct order?

The linked list sounds good if all the data is loaded into objects in the middle tier, but I'm looking for a SQL solution for a set of reports. The results need to be retrieved very quickly directly from the database, in the correct order.

Cheers,

DP

Hey, I design the databases, I leave it to others to get the stuff back out

No, sorry, I realised that. Platform-independent so no PL/SQL, right? I suppose a linked table in 1-1 relation, containing only its own primary key (so the main table can FK to that), the sort sequence, and the foreign key to the main table (not strictly necessary), then actually update the record, and all consequently necessary records, in that table. Have an index on the sort-sequence, and get the data out with a join.

Spacecadet is spot-on in saying that optimising the solution depends on a knowledge of the data and its activity, and of what is regarded as non-optimal: e.g. (lots of data + few updates + fast daytime processing needed + slow overnight batch possible) warrants a different solution from (little data + unknown number of updates + fast retrieval needed + batch undesirable); and different again if even large numbers of updates need to be produced quickly.