Hacker News Reader

Comment by pirsquare | Hacker News Reader

Regarding table columns sorting - are there anyone else who thinks that clicking on the same column the third time should remove the sort? For example:

  • First Click: Sort ASC

  • Second Click: Sort DESC

  • Third Click: Remove Column Sort

This allows me to go back to the initial state where the tables have been loaded. Currently, most data tables don't support "resetting" of column sorting and are implemented in this manner:

  • First Click: Sort ASC

  • Second Click: Sort DESC

  • Third Click: Sort ASC

View on HN

No, I think it should toggle between ASC and DESC, as it does.

Now, one thing that is very important to me: The sort should be stable. So, for example, I should be able to sort by successively by track number, disk number, album, and artist, to get a view that is sorted by artist, album, disk, and track.

Unfortunately, some views (eg iTunes list view) don't obey that.

If there's a "hidden" intrinsic ordering in addition to the by-column orderings, then the third click should indeed reset the data to that intrinsic ordering. A music player playlist is a great example; it's a list, meaning each element tuple is augmented with a unique integer, its index on the list. The user can reorder the elements on the list arbitrarily, and the list must be revertible to the custom order after sorting by any column.

Couldn't that be made explicit by having a list index in the first column?

It depends on the context.

For a music player, "index occurrence in release album" is thoroughly useless data in every situation other than playing albums in order. So it's more elegant to hide that information and specifically design the sorting features around the use case.

It could, and I thought of mentioning it, but I haven't seen it in the wild anywhere. Probably it would just add clutter and look confusing when the list is sorted by something else. The triple-click UI seems to be a fairly established way to do it.

It's interesting, I just checked - in iTunes, if you switch a playlist to View As -> Song, then it does display a first column with numbers 1..N. Now, if you sort after some other column (say, Artist), the first column does not get permuted accordingly, but stays 1..N. However, if you click the first column, it brings the playlist in the original order, and if you click again, it brings it in reverse order (while still displaying 1..N). A bit weird, but effective.

But then, sort is not stable. For example, if I sort by song name, and then by genre, the songs within a genre are not sorted by song name, but (for some unfathomable reason) by artist. Very annoying. Bad iTunes, bad. (This is on macOS Mojave, haven't dared the jump to Catalina yet.)

Just checked and Spotify sorting isn't stable, either. Presumably this is because, as with most apps based on a database of some sort, there may not be a "materialized" in-memory data model that one would sort, but the column to be sorted by is passed to the backing database as a part of a query. The app could, I guess, remember the last few sort-by columns and craft the query accordingly.

However, insofar as most people desire the ability to sort by multiple columns, they'd likely also want a more intuitive UI than clicking the columns in the reverse order of priority. A few years ago I participated in developing a datagrid component where you could add columns to the sort order by shift-clicking.

Very plausible. I'd certainly appreciate the workaround you suggest; and I also agree that the reverse order thing is not intuitive at all. "Add behind" by shift clicking sounds good, but hard to make discoverable...

I think it depends. I like they way it work in Spotify, where the third click on a heading returns to default sort. Most of the time I want it sorted by default order, but when looking for a song I change to order by name and after finding the song I want an easy way to get the playlist back to default sort

This seems orthogonal? It certainly is compatible with what the post you are replying to is suggesting.

After thinking this through for a moment, I think the point here is that there are two fundamentally different ways of thinking about sorting. To the original poster, it's a way of viewing the data. It doesn't change the original ordering of observations in the table, and it makes sense then to be able to turn it off... To the second comment, it is a way of actually changing the representation of the data. That's why you would expect it to be stable and dependent on the order that the sorting was done.

Personally, I like the idea of it being a way of viewing the data rather than a change to the data. It would even make sense to be able to sort by column A, and then click to sort column B to breaks ties, and column C to further break ties ect. (though there would need to be a visual describing the precedence of the sorts to the user). Then, you can undone any of these by clicking a column twice more.

Yes, it's somewhat orthogonal. Having a third click meaning "ignore that I clicked here" is certainly fine. But please be stable :-)

Agree. We have a data config later in our app that stores a manual sorting of attributes. This config is client specific and managed by our data engineers. This is the default sort and is available via this tri-state behavior.

I like your thought but personally I would prefer resetting the state with a right mousebutton or third mousebutton click.

Right click should bring a menu with options, not change state.

3rd mouse should be a shortcut to something that can already be done

If we’re proposing a solution, why not click to toggle ASC/DESC then have the ability to “remove sort” in the right-click context menu?

There's no right / third mouse button for mobile users though.

(long press, though that gesture can get overloaded)

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.

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

It's how e.g. Wikimedia/wikipedia tables behave.

DBeaver have this behavior, and I find it very useful

"Removing the sort" doesn't make much sense. If the column is sorted and you remove the sort, then it will remain sorted.

I guess pirsquare meant going back to default sort by "Removing the sort", which is a very reasonable thing which I personally miss in some software.

Reply on news.ycombinator.com