Best way to create a database table sortable by column

Question

I have a table with unsorted rows and want to make them sorted by column. I want the table to be sorted by column, so I create a sort_by column.

Now the table looks like this:

ID  Data   sort_by
--  -----  -------
27  Blahh  1  
54  Blahh  2  
57  Blahh  3  
1   Blahh  4

Now I want to insert a row in the second position (sort_by = 2). Then I will have to update sort_by for all rows> 2 with sort_by + = 1. This is a lot of updates if I have several million rows.

So, is there a canonical way to do this? Of course, I could take big steps between columns and insert new lines into spaces, etc., But is there an aesthetically pleasing way, i.e. Not a hack?

Sorting is done manually by users. A good phrase for google for = upvote, all I'm trying to do is open ways to sort the table in some DBMSs or some frameworks. :)

Answer 1

Two ways I can think of.

1) Change sort_by to decimal. You can always insert a new line with a large number of decimal places between existing values.

2) Turn your table into a linked list by replacing sort_bywith comes_after_ID. This is a foreign key pointing to YourTable.ID.

ID  Data   Comes_after_ID
--  -----  --------------
27  Blahh  NULL
54  Blahh  27
57  Blahh  54 
1   Blahh  57

In the second design, it would be easier to place an order in the application code, and not in SQL.

In either case, the drag and drop GUI can hide this implementation from the user.

[a] (TM) , . . , .

Answer 2

. XY.

:

SQL?

:

-, ?

, . , .

:

, - - , ,

, ? , , , , , , ...

-- Wacky featured items are random!
SELECT * FROM products WHERE featured = True ORDER BY RANDOM LIMIT 10;
-- More than 20% wow, thats a great deal!
SELECT * FROM products WHERE discount > 0.2 ORDER BY discount ASC LIMIT 10;
-- New stock, tell me about it!
SELECT * FROM products WHERE added_to_database < now()-24hours ORDER BY added_to_database LIMIT 10;
-- These items are about to disappear for ever, buy now!
SELECT * FROM products WHERE stock_number < 20 ORDER BY stock_number LIMIT 10;

, , . , , , , .