Sorting without ORDER BY

Question

It sounds retarded but can it be possible?

I have EMPLOYEE_ID and DEPARTMENT_ID i have to sort it according to the DEPARTMENT_ID without using ORDER BY.

It should not present anywhere in the query i.e in USING clause or in SUB-QUERY or in SELECT statement or in anywhere.

Is it possible?

Thank you.

Answer 1

There are a few approaches that can work depending on particular RDBMS and none of them is to be used in production environment, but just for fun:

1. Use XML output and apply server-side XSLT transformation (through CLR for instance) with <xsl:sort>.
2. Use stored procedure to produce sorted list in one text return value.
3. Write own SQL proxy client replacing -- HIDDEN MESSAGE with ORDER BY. (I admit, this is not exactly SQL solution).
4. Create an Indexed (Materialized) View on the table sorted by DEPARTMENT_ID that would be solely used by this query. Not guaranteed to work every single time.
5. Create temporary table with all possible IDs in incremental order, left join source table on DEPARTMENT_ID and use hints to prevent optimizer from reordering joins. Not guaranteed to work every single time.

Upd 6. When there are fewer rows to sort then the RDBMS supported CTE recursion depth:

With Example (EMPLOYEE_ID, DEPARTMENT_ID) As (
        Select 4, 2 Union All
        Select 5, 2 Union All
        Select 6, 3 Union All
        Select 7, 3 Union All
        Select 2, 1 Union All
        Select 3, 1 Union All
        Select 1, 1
    ),
    Stringified (ID) AS (
        Select
            RIGHT('0000000000' + CAST(DEPARTMENT_ID AS NVARCHAR(10)), 10) +
            RIGHT('0000000000' + CAST(EMPLOYEE_ID AS NVARCHAR(10)), 10)
        From Example
    ),
    Sorted (PREV_EMPLOYEE_ID, PREV_DEPARTMENT_ID,
            NEXT_EMPLOYEE_ID, NEXT_DEPARTMENT_ID) As (
        Select
            CAST(Right(ex1.ID, 10) AS INT),
            CAST(Left(ex1.ID, 10) AS INT),
            CAST(Right(Min(ex2.ID),10) AS INT),
            CAST(Left(Min(ex2.ID),10) AS INT)
        From Stringified ex1
        Inner Join Stringified ex2 On ex1.ID < ex2.ID
        Group By ex1.ID
    ),
    RecursiveCTE (EMPLOYEE_ID, DEPARTMENT_ID) AS (
        Select
            CAST(Right(Min(ID),10) AS INT),
            CAST(Left(Min(ID),10) AS INT)
        From Stringified
        Union All
        Select NEXT_EMPLOYEE_ID, NEXT_DEPARTMENT_ID
        From Sorted
        Inner Join RecursiveCTE
             ON RecursiveCTE.EMPLOYEE_ID = Sorted.PREV_EMPLOYEE_ID
            AND RecursiveCTE.DEPARTMENT_ID = Sorted.PREV_DEPARTMENT_ID
    )
Select *
From RecursiveCTE

Upd 7. Many RDBMS engines would sort result when applying GROUP BY, UNION, EXCEPT, INTERSECT or just DISTINCT especially if they are single-threaded or forced not to use parallelism with a hint. Not guaranteed to work every single time.

Answer 2

That could be possible if you would create a index on your table where first(or only) key is DEPARTMENT_ID and you would force your query engine to use this index. This should be a plain SELECT statement as well.

But even then, it won't guarantee correct sort order.

Answer 3

May be this link would help you

sort results without use of order by clause

This is what the link says

You cannot, at least not reliably.

Some SQL implementations may well return rows in the order of their primary keys or clustered indexes, but SQL itself is a relational algebra that returns arbitrarily ordered sets unless specifically told otherwise.

There's a good chance that the order in which rows are returned may well depend on the insertion and deletion activity since the table was created.

Answer 4

You could make your query without ORDER BY, put every row into a multidimensional array and after that sorting your array. It can be done in several languages.

In PHP it would be something like this:

$result = [];
// In $result you put every row from your SELECT

$aSortField = [];

    foreach ($result as $key => $row)   
        {
        $aSortField [$key] = $row['theNameOfYourSortColumn'];
        }

    array_multisort($aLocal, SORT_ASC, $result);    // This makes the "order by" job