Guddu Guddu - 4 months ago 9
SQL Question

Oracle Order by - Function Based

I have a table that has the following 4 columns with some example data

Aisle | Rack | Level | Position
1 1 1 1
1 2 1 2
2 1 1 1
2 2 1 1
2 3 1 1
3 1 1 1
3 2 1 1


I wish to write a single query that can Sort records using the following


  • First by Aisle (Ascending)

  • Then by Rack (Ascending for one Aisle and
    Descending for the next and so on. . . This is the part that I can't
    seem to figure out if it can be achieved in Oracle
    )

  • Then by Level (Ascending)

  • Then by Position (Ascending)



So the resulting ordered rows will be as follows

Aisle | Rack | Level | Position
1 1 1 1
1 2 1 2
2 3 1 1
2 2 1 1
2 1 1 1
3 1 1 1
3 2 1 1


Is this achievable in Oracle?

Answer

What you need to do is enumerate the aisles, so you can identify the odd and even ones. The function dense_rank() does this -- and it is allowed in the order by clause.

So:

order by aisle,
         (case when mod(dense_rank() over (order by aisle), 2) = 1
               then rack else - rack
          end),
         level, position

Actually, if aisle has no gaps, you could also use:

order by aisle,
         (case when mod(aisle, 2) = 1
               then rack else - rack
          end),
         level, position