Hacker News Reader

Comment by dmos62 | Hacker News Reader

Rows will always have an order, even if that order is the order they are indexed in the database, which for the purposes of a table is an implicit column. So "remove column sort" means revert to the default sort. In other words, there is no such thing as no sort: rows will always be sorted, you just choose by which column and the direction. Implicit sorts aren't a great idea, I think.

View on HN

That behaviour will be database-specific, most databases I've seen simply say that the order is undefined if there is no explicit "order by" clause.

Imagine a query like "select top 10 * from foo where bar = 1", when there is no index on the bar column. The database will need to scan through the foo table, the check the bar column for each item, then return the first 10 that it finds. So it could behave as you describe, just scanning from the top to the bottom in on-disk order, returning results in that order.

However, the database may maintain statistics of how often bar is set to 1, and if it is only sparsely set, it could recognise that a single-threaded top-to-bottom scan would be quite slow. In this case, it could be much faster to break the table up, one chunk per thread, scan each chunk, then return results as soon as enough are found. The final order of results (if there is no order by clause) could then depend on the order that the threads run in, but you'd only see that if there was a lot of data in the table to start with.

https://docs.microsoft.com/en-us/sql/t-sql/queries/top-trans...

This is why it is very common to see suggestions to always specify an explicit order when implementing pagination, as the order of results could change from one page to another if the sort order is left undefined, leading to missing or duplicated results from a user's perspective.

I'd agree, but only if you a) include the column you initially sort by[0], and b) have the UI show that the table is sorted by that column. This way, there's always a sorting marker that's only flipping and moving between columns as you click on them.

[0] - Even if it means including a DB id, though preferably, you'd resort the data by one of the columns intended for users.

Almost all tables I've worked with are sorted by the first column by default. So 'remove custom sort' simply means to go back to the sort on the first column. It isn't some nebulous implicit sorting from the database, it is going back to the state before you changed the sort.

I agree; in projects where I've used a lot of data tables, there is usually a column that makes sense as the default sort for the stakeholders, so I just make sure it is ordered by that at load and then it makes more sense to people to toggle ASC/DESC sorts.

Thanks - you certainly made a good point. But there's one problem with enforcement of explicit sort and this the problem I constantly have with my projects.

By default, most tables that I have are sorted by "created_datetime" but this is the column that won't be shown to the end-user since most of the time it is redundant information for them. If we're implementing explicit sort, I need to add an additional column which would be useless to them.

Having the ability to easily reset back to default sort makes everything so much cleaner as an end-user (for me). I won't have to experience another "How the heck do I go back".

Depending on the dataset structure (as seen by app/db developer) there may be in fact two sorts: one that reorders rows in a set* and one that reorders rows in a table view (M and V from MVC, respectively). Your “go back” thing is then a conflict between the two.

It may be seen as overengineering for an itunes user, but a full implementation should provide a way to do both sorts and to cancel view-sort if no longer needed.

Also, sorts may be not just column-based. Shop cart may be sorted by product group without having such column even in dataset, like rows.sort(“product.group.title”) or alike. In UI it can be done as right-clicking on a column/cell and sort->[natural, id, title, group->[natural, id, title, ...], ...] menu hierarchy.

* algorithms may depend on this ordering, so a user takes care to not change it accidentally

Reply on news.ycombinator.com