Oracle Order by - Function Based

Question

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 1

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

Oracle Order by - Function Based

Sign up