Sorting without ORDER BY

Question

It sounds retarded but can it be possible?

I have EMPLOYEE_ID and DEPARTMENT_ID and 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?

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.