CODEWITHSUNDEEP
sqlrdbms
rickythefox
rickythefox

Reputation: 6851

Best way of creating a database table sortable by a column

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

The table now 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 second position (sort_by = 2). Then I'll have to update sort_by for all rows > 2 with sort_by += 1. That's a lot of updates if I have a few million rows.

So, is there a canonical way of doing this? Of course I could have larger steps between columns and insert new rows in the gaps etc, but is there an aesthetically pleasing way, i.e. not a hack?

The sorting is done manually by users. A good phrase to google for = upvote, everything I try turns up ways to sort table in some DBMS or some framework. :)

Upvotes: 5

Views: 451

Answers (2)

user764357
user764357

Reputation:

No one sorts a million rows by hand. What you have here is a classic XY Problem.

You are asking:

How can I sort a million rows in an SQL database?

When you should be asking:

How can I promote certain products in an online shop that is managed in a traditional Relational Database Management System?

The former requires a bunch of hacky solutions such as weightings, or decimal columns with ever widening precision, or in database linked lists. All of which are prone to error are counter-intuitive and ignore the problem domain.

You comment says:

Think products you want to present on the start page of a web shop - what the marketing team thinks will sell

Well, what kinds of products do I want on the front page? Featured products, products that are on sale, products that are new, products that about to go out of stock...

-- 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;

Think about why you want to order them, not how to order them. This way you can get just some based on some criteria, and it more closely models the domain you are working in.

Upvotes: 1

Michael Green
Michael Green

Reputation: 1491

Two ways I can think of.

1) Change sort_by to a decimal. You will always[a] be able to insert a new row with more decimal places between existing values.

2) Turn your table into a linked list by replacing sort_by with 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

With the second design it would be easier to do the ordering in application code rather than SQL.

Either way a drag-and-drop GUI can hide this implementation from the user.

[a] Always(TM) is limited to the resolution of decimals on your system, of course. Eventually you will have to re-sequence your items. But this can be done as a monthly job rather than as the user processes input.

Upvotes: 0

Related Questions