Hello,

I'd like to condition the sort order of a query.
Let's give an example:
For sure this doesn't work.
Could someone tell me how can I do that ?
Is there an EVAL function which allows to do that ?
Like
(I'm in a procedure)

We don't order by expressions, only derived columns (by name or index).

The solution you're looking for is something like this

You mean you don't use to do that or it's not doable ?
Because actually this
works

Finally I'm going to make a view,
so it will be more maintainable.

As you've noticed, it is possible to order by an expression. It's also possible for me to take a bath with the toaster, doesn't mean I want to do it!

Essentially the SQL standards state that you should order order by derived values, by their name/alias or their index (first column = 1, second column = 2, etc) - which is why we don't do it.

...and just to sound like (more of) a bore - you can't create views with an ORDER BY clause!


You can easily create the view without that bit, and then when you select from the view add the order by clause; e.g.

Yes, that's what I wanted to do.

Why is that? Is it a performance thing? Ordering of data is most likely to be done on data that is about to be displayed to the user. The amount of data to be displayed would normally be quite small (less than 100 records, preferably less than 20 records) otherwise the user will just ignore the report and use it instead as a paper weight. I see how you order the data to be more a business requirement rather than a performance requirement so if the business wanted the data to be displayed in order of field x that's exactly what they'll get - most likely they'll want the ability to order by just about every field that's on the display.

If you're doing this with a stored proc you could just do it this way which might be slightly more easier to read (ie more maintainable) :

Code:

SET condition = TRUE;

if condition then
   SELECT id, name
   FROM table_1
   ORDER BY name
else
   SELECT id, name
   FROM table_1
   ORDER BY name DESC
end if;

Other options to consider if you're outputting the data to some report via an external program such as PHP might be to just build the final select statement in PHP depending on your condition.

Code:

$condition = TRUE;

if ( $condition ) {
   $sql = "SELECT id, name FROM table_1 ORDER BY name"
} else {
   $sql = "SELECT id, name FROM table_1 ORDER BY name desc"
}

Of course if you were using a numeric value to order by then it would be easy enough:

Code:

SET condition = TRUE;

SELECT id, name
FROM table_1
ORDER BY IF( condition , val , val * -1)

Mike

It's a standards thing
As for performance - if you think about how a query executes, the very last step is generally the order by, right? Which means an expression would have to be re-evaluated again.

...in theory - this is one I've not proved yet but I could do if anyone is interested enough (proof would be SQL Server )

Who's standards? So you're saying that if the business wants to be able to order it's report by any field on screen then you would simply say no and tell them you can't because "it's a standards thing"???? You're braver than me!

So what? that's hardly a reason to refuse to do it. If the requirement is for a report to ordered by an expression then that's what you provide. If you can suggest a better way of doing things then great but if I'd asked for a certain ordering and you said you wouldn't provide it because it means an expression would have to be re-evaluated again then I'd be having a quite word with your boss.

ANSI / ISO standards (sorry for not being clearer earlier)

As for examples of how to remove the expression from the order by - see post 2 - it contains two variations of this theme.

I think we'll have to agree to differ on what's more maintainable then. To me example 2b looks like a typical NZDF solution to ordering and the more fields that are candidates for ordering then the more complex that query becomes. I'm guessing that in the least complex case then your example might be marginally faster but I doubt if you'd be able to measure the difference in real time. I certainly know which example I'd use if it was me that had to maintain the code.

Perhaps it's just me seeing things differently to the rest of the civilised world ... again.

You appear to have overlooked the subtlety in the second example...

The first query returns the derived columns as part of the resultset while the second example does not. Both examples are ordered correctly based on the a parameter setting.

I think the fact it was too subtle for me indicates it might be a maintenance headache in future

I know my example used a choice of two selects rather a single complex one but the example below is pretty quick to understand and would work in a sproc :

Code:

if condition then
   SELECT id, name
   FROM table_1
   ORDER BY name
else
   SELECT id, name
   FROM table_1
   ORDER BY name DESC
end if;

I like that suggestion too Mike. It will be the best performer of the lot because it only has a single order by (and no computations either).