Reply to: Generic SQL and custom sort orders

[PerlOfWisdom]

Number the sort order column in even numbers, then you can move an item between two others by using an odd number. You only need to renumber between the movedFrom and movedTo records.

[expat]

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.

At that rate* I'd take a brute-force approach and just update the entire table's sequence-numbers every time you move an item. And index on the sequence-number.

* i.e. few updates so you can stand it being slow; only 1000 items so an update won't be that slow in human terms; reads are high so index on a column will be the fastest way to do that.

PS and eliminate any indexes that you don't need, if any: no need to update them every time.

[jambuie]

You could use a CASE statement to do this.

E.g:

CASE WHEN manufacturer = 'Ford' THEN 1
WHEN manufacturer ='Toyota' THEN 2
WHEN manufacturer ='Vauxhall' THEN 3
WHEN manufacturer ='Mazda' THEN 4
WHEN manufacturer ='Saab' THEN 5
WHEN manufacturer ='Porsche' THEN 6
WHEN manufacturer ='Mercedes' THEN 7
WHEN manufacturer ='Suzuki' THEN 8
WHEN manufacturer ='Honda' THEN 9
WHEN manufacturer ='BMW' THEN 10
END as sort_order

and then sort on that calculation.

[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.

[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]

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).

[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.

[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.

[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]

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.

[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]

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]

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]

Okay here's some example data, just has a piece of text and a desired display position

"Ford", 1
"Vauxhall", 2
"Honda", 3
"Toyota", 4
"Mazda", 5
"Saab", 6
"Porsche", 7
"Mercedes", 8
"Suzuki", 9
"BMW", 10

If I move Honda down to 9th position, it should push Suzuki up:

"Ford", 1
"Vauxhall", 2
"Toyota", 3
"Mazda", 4
"Saab", 5
"Porsche", 6
"Mercedes", 7
"Suzuki", 8
"Honda", 9
"BMW", 10

If Toyota moves up one we get:

"Ford", 1
"Toyota", 2
"Vauxhall", 3
"Mazda", 4
"Saab", 5
"Porsche", 6
"Mercedes", 7
"Suzuki", 8
"Honda", 9
"BMW", 10


If BMW moves to 2nd place, it should move Toyota down and we get:

"Ford", 1
"BMW", 2
"Toyota", 3
"Vauxhall", 4
"Mazda", 5
"Saab", 6
"Porsche", 7
"Mercedes", 8
"Suzuki", 9
"Honda", 10


So I think I can do it with a simple sortOrder column, where I have to update the sortOrder value for all rows between the original position and the new position, and either move the displaced one up or down depending if the original is above or below the target item.

At least the SELECT SQL stays simple and fast and by putting an index on the sortOrder column, fast. The downside is a lot of rows need to be updated if an item moves a long way up or down, but this I can live with.

If I went for a linked list solution, what would the SELECT SQL look like?

[maverick]

Is that the Mighty Whoooosh!