SQL Best Practice to Deal With Default Sort Order

SQL best practice to deal with default sort order

There is no default sort order. Even if the table has a clustered index, you are not guaranteed to get the results in that order. You must use an order by clause if you want a specific order.

What's the best way to store sort order in SQL?

None of the answers so far have touched on the real problem with custom sort order and that is what happens when two different people want the same records sorted differently.

If you need a custom sort order, you need a related table to store it in, not an additional field. The table would have the userid, the recordId of the data and the sort order for the record. That way Joe Smith can have one order and Sally Jones another for the same data. Now you have the problem of new records being added to the data set. Do you put them at the beginning of the sort order or the end or do you require the person to set an order for them before they can be added to the set. This is in actuality a very complex problem that is generally not worth the amount of time it takes to implement because almost no one ever uses that system once it's in place (I mean do I really want to go through a hundred records and mark the individual order of each one?). Now it gets complicated in terms of saving the order of all the records (which will of course require changes the next time the query is run since there will be new records.) This is very painful process of limited untility.

I did this once in a proposal writing application because we needed to be able to sort the parts and tasks on the proposal in the order we thought would be most impressive to the customer. Even then, we had to institute a default order, so that they only need to move around the two or three things they really wanted to show up first instead of ordering 10,000 individual parts.

A better choice if you can get them to buy off on it, is to allow them to sort the data by columns (desc or asc). Usually the user interface can be designed so that if you click on a column header, it will resort the data by that column. This is relatively straightforward to do and meets most needs for custom ordering.

You really need to discuss this requirement with management and get details of how they want it to work beyond, I want custom ordering. This is often one of those things people think they want, but don't really use.

Override SQL Server Default Sort Order

This will force Alpha over Digits

SELECT TOP 1 order_id
FROM orders
WHERE order_id IS NOT NULL
ORDER BY case when order_id like '[0-9]%' then 1 else 0 end
        ,order_id

Does A Default Sort Order in DAL Violate Separation of Concerns

I feel it's OK if you have a 'default' sort setting which is applied in the absence of sort settings being provided to the procedure.

That will make the code re-usable and flexible going forwards....what if, for example, you wanted to allow users to define the sort settings themselves?

I don't think it's a violation, considering many tables have obvious default sorting logic that would otherwise need to be re-applied time and time again. And you are correct in saying it's more effective to apply sorting at the database level.

Questionable SQL practice - Order By id rather than creation time

In typical practice you can almost always assume that an autoincrement id can be sorted to give you the records in creation order (either direction). However, you should note that this is not considered portable in terms of your data. You might move your data to another system where the keys are recreated, but the created_at data is the same.

There is actually a pretty good StackOverflow discussion of this issue.

The basic summary is the first solution, ordering by created_at, is considered best practice. Be sure, however, to properly index the created_at field to give the best performance.

Default row ordering for select query in oracle

According to Tom Kyte: "Unless and until you add "order by" to a query, you cannot say ANYTHING about the order of the rows returned. Well, short of 'you cannot rely on the order of the rows being returned'."

See this question at asktom.com.

As for ROWNUM, it doesn't physically exist, so it can't be "freed". ROWNUM is assigned after a record is retrieved from a table, which is why "WHERE ROWNUM = 5" will always fail to select any records.

@ammoQ: you might want to read this AskTom article on GROUP BY ordering. In short:

Does a Group By clause in an Query gaurantee that the output data will be
sorted on the Group By columns in
order, even if there is NO Order By
clause?

and we said...

ABSOLUTELY NOT,

It never has, it never did, it never
will.

Is there a default sort order for cursors in COBOL reading from DB2?

There is no default sort order for results returned from a DB/2 select statement. If you need, or expect, data to be
returned in some order then the ordering must be specified using an ORDER BY clause on the SQL predicate.

You may find that results appear to be ordered but that ordering is just an artifact of the access paths used
by DB/2 to resolve the perdicate. Simple queries requiring only stage 1 processing are often resolved using an index
and these are typically ordered
because the undelying index follows that order. This is totally unreliable and may change due to a
rebind causing a different access path to be used or when the underlying index is in need of being rebuilt (after
many insertions/deletions, lack of free space etc).

Queries that require stage 2 processing tend to come out ordred, but this too is just an artifact of query resolution
and should never be relied upon.

COBOL does not excercise any inherent control over DB/2 operations other that what may be achieved using SQL alone.

---

Related Topics