Oracle Specific Sorting

Question

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.

Where in your query are you using random numbers? Where/how is rownum defined or generated? — Dai
– Dai, Commented Nov 5, 2019 at 12:35
Tip: You don't need to use a nested CASE WHEN: you can make rownum < 3 the second case in the top CASE WHEN expression. — Dai
– Dai, Commented Nov 5, 2019 at 12:36
By random I mean that is not important how other products are sorted. I only want them to be in between 1 - no. of products where one of the rows is specified by me. I hope I'm clear. — Ergi Nushi
– Ergi Nushi, Commented Nov 5, 2019 at 12:38

Gordon Linoff · Accepted Answer · 2019-11-05 12:42:56Z

3

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;

edited Nov 5, 2019 at 12:42

answered Nov 5, 2019 at 12:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ergi Nushi Over a year ago

Thanks for your reply. Randomness is not enforced in fact. I only need that the specific product to be in the specific place I imply. Other products can be in a random order in between 1 - no. of products I have. Like I mentioned in my question.

Gordon Linoff Over a year ago

@ErgiNushi . . . That is not an important part of this answer, but your question does specific say "random", so I included it.

Ergi Nushi Over a year ago

After making a few changes this worked perfectly. Thank you very much :D

Collectives™ on Stack Overflow

Oracle Specific Sorting

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related