CODEWITHSUNDEEP
mysqlsqlsortingsql-order-bymysql-workbench
Sweet Chilly Philly
Sweet Chilly Philly

Reputation: 3219

MY SQL Workbench: Default ORDER BY

I have looked around and can't find an answer to this.

I was wondering if there is a way in MY SQL Workbench where you can add a default ORDER BY to every query?

I am looking to ORDER BY id DESC

For example when I 'Right Click' on my table schema and say 'Select Rows' the resulting query is:

SELECT * FROM payments

I would like it to be:

SELECT * FROM payments ORDER BY id DESC

Upvotes: 6

Views: 4860

Answers (3)

Oleksiy Guzenko
Oleksiy Guzenko

Reputation: 41

Solution above is good, but it doesn't change first default "Select Rows" in menu, you need to click "Send To SQL Editor"-> "Select All Statements". I would like to create such plugin, but there are no many tutorials with plugin creation for WB especially how to insert new line to context menu or how redeclare select_all_statements() function.

Here is my hack, where it orders by default with first column (usually this column is Primary key in most cases)

parts.append("SELECT %s\nFROM %s.%s ORDER BY %s DESC;\n " % (",\n    ".join("%s.%s" % (esc_ident(obj.name), esc_ident(c[0])) for c in self.get_table_columns(obj.schemaName, obj.name)), esc_ident(obj.schemaName), esc_ident(obj.name), esc_ident(self.get_table_columns(obj.schemaName, obj.name)[0][0]) ))

Upvotes: 3

Philip Olson
Philip Olson

Reputation: 4850

This won't be the answer you're looking for, but I'll add it for those that are curious. In the future I'll investigate how to make this its own plugin. Maybe.

For this exercise, let's modify Workbench's "Select All Statement" behavior. You see "Select All Statement" in the context-menu when right-clicking on a table, and after choosing either "Copy to Clipboard" or "Send to SQL Editor".

On my system (OS X), this is defined here:

/Applications/MySQLWorkbench.app/Contents/Resources/plugins/sqlide_schematree_ext.py

Specifically, this code:

(_("Select All Statement"), 'select_all_statement', ['db.Table', 'db.View'], len(selection) > 0, False),
(_("Select All Statement"), 'select_all_statement', ['columns'], len(selection) == 1 and selection[0].type == 'columns', False),

References this code:

def select_all_statement(self):
# assumes only table nodes (or the Columns node of a table)
parts = []
for obj in self.selection:
    if obj.type == 'columns':
        obj = obj.owner
    parts.append("SELECT %s\nFROM %s.%s;\n" % (",\n    ".join("%s.%s" % (esc_ident(obj.name), esc_ident(c[0])) for c in self.get_table_columns(obj.schemaName, obj.name)), esc_ident(obj.schemaName), esc_ident(obj.name)))
self.send("\n".join(parts))

Well, I'm not yet sure how to make this intelligent (e.g., figure out the PK, or offer column suggestions in the context-menu) but for now let's add dumb code (i.e., ORDER BY id). You could modify the parts.append line to something like this:

    parts.append("SELECT %s\nORDER BY id\nFROM %s.%s;\n" % (",\n    ".join("%s.%s" % (esc_ident(obj.name), esc_ident(c[0])) for c in self.get_table_columns(obj.schemaName, obj.name)), esc_ident(obj.schemaName), esc_ident(obj.name)))

Like I said, it's not intelligent. Hopefully one day I (or someone) updates this to be more intelligent. That said, restarting Workbench will make the above change show up in Workbench because Workbench recompiles plugins (and most everything in Workbench is a plugin) at startup. Feel free to change "Select All Statement" to "Select All Statement and Order by id" or anything else. Or, add new entries and a new function, such as select_all_and_order_statement. Happy hacking! :)

Upvotes: 3

O. Jones
O. Jones

Reputation: 108841

is there a way in MY SQL Workbench where you can add a default ORDER BY to every query?

No.

What's more, result sets from SQL queries present their rows in a formally unpredictable order in the absence of ORDER BY clauses sufficient to determine the order. A lot of people get tripped up by this: they assume that because a result set appeared in a certain order yesterday, it will appear in the same order today. It does until it doesn't. If a program relies on ordering when it isn't specified in an ORDER BY clause, well, see Murphy's Law.

Ditto for software versions. People sometimes mistakenly believe it's a defect in a new version of a RDMS server like MySQL when it presents results in a different order from the preceding version. It isn't.

This can help performance. Ordering can be a time- and space- hog of an operation. When the query planner knows it doesn't have to order rows in a result set, it can just present them in whatever order it finds them.

Upvotes: 6

Related Questions