CODEWITHSUNDEEP
sqloracle-database
Ergi Nushi
Ergi Nushi

Reputation: 863

Oracle Specific Sorting

I have the following problem: I need to sort some products where one needs to be a specific row and others to be random.

So if I have products: A B C D, I need for example B to be the third product while others can be random like:

C  1
A  2
B  3
D  4

Best shot I have tried is (3 is a dynamic value):

SELECT
    product_name,
    CASE
        WHEN product = 'B' THEN 3
        ELSE ( CASE WHEN rownum < 3 THEN rownum ELSE rownum + 1 END )
    END sorting
FROM
    products
ORDER BY
    sorting ASC;

but I'm not always getting the desired outcome.

Any help or lead is appreciated.

Upvotes: 2

Views: 106

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

This is rather tricky, but you can use row_number() and a bunch of arithmetic:

select p.*
from (select p.*,
             row_number() over (order by case when product = 'B' then 2 else 1 end),
                                         dbms_random.value
                               ) as seqnum
      from products p
     ) p
order by (case when seqnum < 3 then seqnum end),
         (case when product = 'B' then 1 else 2 end),
         seqnum;

The logic is:

  • Enumerate the values randomly, with the special value going last.
  • Put in the rows with lower values.
  • Put in the row with the special value.
  • Put in the rest of the rows.

The above uses a subquery because the randomness is enforced. You can do this without a subquery as:

order by (case when row_number() over (order by (case when product = 'B' then 2 else 1 end) < 3
               then dbms_random.value
               else 2  -- bigger than value
          end),
         (case when product = 'B' then 1 else 2 end),
         dbms_random.value;

Upvotes: 3

Related Questions