Skip to content

[Discussion] Multi-column sorting #1409

Closed

Description

@Llaves

at the suggestion of @justinclift , I'm including some thoughts on implementation.
Bearing in mind I have zero familiarity with the code:

It occurs to me this is a more complex issue than I would have
thought.

The log view suggests that column sorting is implemented by executing
an SQL query with "ORDER BY". The sort appears to be stable with
respect to table ordering, but the SQL standard explicitly states that
it is not a stable sort because a table is not defined as having an
ordering. So how do you achieve a multi-column sort? You could keep
track of column sorts and write a multi-column SQL statement, but this
is pretty clunky. How far back in the list do you go? How does a user
clear the list to get a sort on a single col? And of course, sorting
the same column twice in a row reverses the order, but what would that
mean if we are keeping track?

Another approach would be to create a temporary table with the sort
command and count on the fact the sqlite appears to use a stable sort.
This would die if sqlite ever dropped a stable sort because the
standard doesn't require it. Also, using a temporary table creates a
problem with the delete record button, since we're now viewing a temp
table, not the actual table in the database.

I suspect the best solution would be a sort dialog, kind of like
excel. You select the columns you want to sort and the direction
(up/down). This dialog then generates the appropriate SQL command.
Of course, you probably know all this already and that's why it hasn't
been implemented.

Activity

  1. justinclift commented on Jun 3, 2018

    @justinclift
    Member

    Thanks @Llaves. Multi-column sorting would indeed be useful. It does get asked about every now and again, so you're not the only one who'd find it useful. 😄

  2. sky5walk commented on Jun 3, 2018

    @sky5walk

    Try appending the fields you want to sort by in the Order By clause. This is the beauty of the Execute SQL panel. And you can save them in a project file for ease of editing. Or cut and paste the log entry into the Execute SQL panel as a jump start if no project.
    My opinion is the SQL is more flexible while you wait for customized gui code.
    Sort F1 asc, F2 desc, F3 asc, etc. gets pretty complex when the user is clicking each field out of sync. Whereas, the SQL query would be entirely repeatable.

  3. justinclift commented on Jun 4, 2018

    @justinclift
    Member

    Sort F1 asc, F2 desc, F3 asc, etc. gets pretty complex when the user is clicking each field out of sync. Whereas, the SQL query would be entirely repeatable.

    That's true. Some kind of specialised sort dialog might be able to do things really well for new (non-SQL) users though. Just from the ease-of-use point of view. 😄

  4. Llaves commented on Jun 4, 2018

    @Llaves
    Author

    It's more than an ease of use thing. The resulting grid/table/whatever in the Execute SQL panel is read-only, whereas the data in the browser is editable. You can change the contents of a cell, delete a row, create a new record. To achieve any of these in the Execute SQL panel you would need to write the appropriate query, truly upping the ante.

  5. chrisjlocke commented on Jun 5, 2018

    @chrisjlocke
    Member

    The resulting grid/table/whatever in the Execute SQL panel is read-only,

    Not entirely true. You can use the button with tooltip 'Save the results view' to save the results as an SQL view and then you will be able to follow the instructions in #141 (comment) to update that view from the 'Browse Data' tab.

  6. Llaves commented on Jun 5, 2018

    @Llaves
    Author

    @chrisjlocke - I just tried this and can't seem to get it to work. I sorted the table in the Execute SQL panel using a primary and secondary key. The results view shows the table sorted as expected. I then saved the output as a view. I open the view in the browser and what I see is the table in the original unsorted order. Perhaps I'm missing something? Version 3.10.1, downloaded 6/3/2018. Thanks

  7. justinclift commented on Jun 5, 2018

    @justinclift
    Member

    It's probably best to use a nightly build instead of the last release:

        https://nightlies.sqlitebrowser.org/latest/

    Lots of improvements and bug fixes in them. Not sure if it'll help in this specific case, though I suspect it will. 😄

  8. Llaves commented on Jun 5, 2018

    @Llaves
    Author

    same result with nightly build.
    I tried saving a view that was a subset of the table, and that displayed the expected result in the browser window.
    I poked around a little more and then found the problem.
    I created a sorted view and called it "sorted". (original, eh?)
    Then I opened the view in the browser. Not sorted. Then I looked at the SQL executed by the app to create the browser display -
    SELECT rowid,* FROM sorted ORDER BY rowid ASC LIMIT 0, 50000;
    No wonder the ordering is lost!

  9. MKleusberg commented on Apr 1, 2019

    @MKleusberg
    Member

    Just stumbled upon this. We have implemented multi-column sorting in the meantime 😄 If you're still interested in this you can try the latest nightly build and sort by multiple columns by holding the Ctrl key.

  10. Llaves commented on Apr 2, 2019

    @Llaves
    Author

    I'm trying with 3.11.1 and not seeing the sort (or doing it wrong). I click on a column heading and the table sorts by that column. I then control left-click on another column and a sort arrow appears in that column, but the table doesn't resort (and that column is not in order within identical entries for the first column clicked. In addition, all entries in the original column, the second column, and columns in-between are highlighted as selections.

    I'm on Windows 7, so the nightly .appImage files are not useful, as far as I can tell.

  11. justinclift commented on Apr 2, 2019

    @justinclift
    Member

    Ahhh, you'll want our Windows nightly builds:

        https://nightlies.sqlitebrowser.org/latest/

    The multi-column sorting is a new thing (still being refined), though it should work reasonably ok already. It was only added in the last few weeks, so isn't in the 3.11.x series releases. 😄

  12. Llaves commented on Apr 2, 2019

    @Llaves
    Author

    That did the trick. Very nice esp the part that the column headings show the priority of the sort fields. Given that everyone is used to clicking on a column heading to sort, it will be a challenge to get the word out that ctrl-click gives a secondary, tertiary, ... sort key. What will be great is when others pick up on this convention and make it a standard feature of tables everywhere - web tables, etc.

    BTW - the reason I didn't spot the Windows nightlies is because I started on the github page, scrolled down to where it said Windows releases, followed that link to the Releases page, which starts with the nightly .appImage builds, then has the release builds. Looking back, I see I scrolled right past the nightlies link on the github project home page. You might consider changing the continuous build section of the windows release page to point to the nightlies on sqlbrowser.org rather than to the .appImage downloads. Minor nit, at worst.

  13. justinclift commented on Apr 2, 2019

    @justinclift
    Member

    BTW - the reason I didn't spot the Windows nightlies ...

    Good point. The continuous build Linux bit is a new thing, and we haven't really thought through how to make it work ok.

    If we change the initial "Windows" link on the front of our GitHub page to point here instead, do you reckon that'd be the right approach?

  14. Llaves commented on Apr 2, 2019

    @Llaves
    Author

    That would probably be a good change. It would reduce the need to maintain the list of Windows builds in two places like you do now, plus it would put the user on a page with links to the Windows/Mac nightlies, which is not obvious on the current Releases page.

  15. justinclift commented on Apr 8, 2019

    @justinclift
    Member

    Cool. Done (a few days ago). 😄

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Type

No type

Projects

No projects

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions