A Sort Table Changes


A Sort Table Changes

Hi Gurus,

    Could you help me with the problem below?

    I have created a SET table with two columns: user VARCHAR(12) and prob FLOAT.

    I would like to acquire 3000000 user with bigger prob.

    So, I do like this:

    Step 1,

    SELECT (ROW_NUMBER() OVER (ORDER BY prob DESC) ) AS prob_rank

                user, ...

    Step 2,

    SELECT the user with the condition prob_rank <= 3000000

    The problem is that the result of the first step changes everytime I repeat Step 1. 

    I would like to ask why it happens and what can I do to achieve my goal.

    I am new here and I am not sure if I have expressed the problem clearly. If there is any doubts or ideas, please leave me a message below.

Thank you.

Tags (3)

Re: A Sort Table Changes

I thnk you are asking prob_rank is changing. It changes  with data increment or decrement. Maybe I misunderstand your question.

Teradata Employee

Re: A Sort Table Changes

If there are equal Prob values, there is no determinism about what order the users will sort. If you need the same rownumber for the same user, you may have to ORDER BY both prob and user.

Also note that you can use the QUALIFY clause in the first select rather than having to store the result of the whole set with rownumber and then run the second select to get the subset you wish.

Re: A Sort Table Changes

Yes, the problem is that the prob_rank is changing. I have checked the data, and there is little equal prob values.