Home > Default > Row_number() is non diterministic.

Row_number() is non diterministic.

November 30Hits:0
Advertisement
hi,
empid frist name sal DEPARTMENT_ID
123     Shanta     7000          50
122     Payam     8000          50
120     Matthew 8000          50
121     Adam     Fripp 8200 50
SELECT
Department_id, first_name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary desc) RN_NO
FROM employees
ORDER BY department_id, salary
i read that row_number is non deterministin so i show that if it is non deterministic then also i will have
empid frist name sal DEPARTMENT_ID RN_NO
123     Shanta     7000          50 4
122     Payam     8000          50 3
120     Matthew 8000          50 2
121     Adam     Fripp 8200 50 1
or this
empid frist name sal DEPARTMENT_ID RN_NO
123     Shanta     7000          50 4
122     Payam     8000          50 2
120     Matthew 8000          50 3
121     Adam     Fripp 8200 50 1
that is empid 122 and 120 may get 2 or 3 but empid 121 will get 1 and 123 will get 4 RN_NO.
please correct me.
yours sincerely

Answers

Hi,
944768 wrote:
hi,
empid frist name sal DEPARTMENT_ID
123     Shanta     7000          50
122     Payam     8000          50
120     Matthew 8000          50
121     Adam     Fripp 8200 50
SELECT
Department_id, first_name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary desc) RN_NO
FROM employees
ORDER BY department_id, salary
i read that row_number is non deterministin so i show that if it is non deterministic then also i will have
empid frist name sal DEPARTMENT_ID RN_NO
123     Shanta     7000          50 4
122     Payam     8000          50 3
120     Matthew 8000          50 2
121     Adam     Fripp 8200 50 1
You may have those results. There is no guarantee that you will ever get the results above; you might happen to always get the results below. Then again, you might happen to get the results above every time. You just can't rely on getting one set or the other.
or this
empid frist name sal DEPARTMENT_ID RN_NO
123     Shanta     7000          50 4
122     Payam     8000          50 2
120     Matthew 8000          50 3
121     Adam     Fripp 8200 50 1
that is empid 122 and 120 may get 2 or 3 but empid 121 will get 1 and 123 will get 4 RN_NO.
please correct me.There's nothing to correct; you seem to have it right.
<tt> ROW_NUMBER () OVER (PARTITION BY x ORDER BY y) </tt> will be non-deterministic among rows that have exactly the same values of both x and y.
In your example, there is only 1 row with depatment_id=121 and sal=8200, so that row will always get the same ROW_NUMBER.
In your example, there are more than 1 rows with depatement_id=50 and sal=8000, so you can't be sure which of those will be assigned the number 2, and which will get 3.

Read other 15 answers

Tags:

Related Articles

  • Row_number() is non diterministic.November 30

    hi, empid frist name sal DEPARTMENT_ID 123     Shanta     7000          50 122     Payam     8000          50 120     Matthew 8000          50 121     Adam     Fripp 8200 50 SELECT Department_id, first_name, salary, ROW_NUMBER() OVER (PARTITION BY de

  • Not right data when row_number used in inner-view sql query...October 11

    Hi , I use the below sql statement which displays the right data select CODE_FARMAKOU,EMP_NAME,PACKTYPE,PACKSIZE , SUM_POSOTITA , row_number() over(partition by code_farmakou order by sum_posotita desc) from   (SELECT CODE_FARMAKOU,EMP_NAME,PACKTYPE,

  • Is that important column order in a query with row_number functionOctober 11

    Hi folks, I am using Oracle 11g R2 on HP-UX machine. I have 2 types of query with row_number and I think they are same but output of each of them are different. I changed only column order in query2. Query 1 : (SELECT "LOOKUP_INPUT_SUBQUERY".&qu

  • Use of ROW_NUMBER() function in PL/SQLOctober 11

    I have a Table Emp with the following Structure SQL> desc emp Name Null? Type EMPNO NUMBER(2) ENAME VARCHAR2(50) HIREDATE DATE DEPTNO NUMBER(2) If I write a following query on this table SQL> SELECT deptno, hiredate, record_id 2 FROM (SELECT deptno,

  • Row_number() -SQL function throws error - SAP B1 2005 B - PL 36October 11

    hi experts, when i try 2 run "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CardName) AS RowNo,CardCode,CardName FROM OCRD Where CARDTYPE='C') AS OCRD" query in SAP B1 2005 B, PL-36 it shows an error -server throw an exception. anybody can c

  • How to update data according to row_number in sqlOctober 11

    hi, after deleting an item I need to update data according to row_number. in oracle database I have a code like this... working perfectly.. update regitem set line_sn= LPAD(ROWNUM,2,'0') where doc_no='" & mydocno & "' how it will work in

  • Can we implement the functionality similar to ROW_NUMBER() in generic/common way for all databases using SQL ?October 11

    In my project,i am allowing user to select database of his choice from given options & i need to write queries considering all these optional databases. So as per my one of requirement,is there any way to achieve generic functionality similar to ROW_

  • Update column with ROW_NUMBER() valueNovember 30

    I have a column that I would like to update with a ROW_NUMBER() value partitioned by a specific column. CREATE TABLE ##tmp (id INT NULL, value1 VARCHAR(10), value2 VARCHAR(10)) INSERT INTO ##tmp (value1, value2) VALUES ('A', 'asdfasdf'), ('A', 'asdf'

  • Use ROW_NUMBER as a join to in a query.November 30

    Hello I have created the following code from assistance on a previous post which works great ;WITH C AS SELECT ROW_NUMBER() OVER(PARTITION BY Introducer ORDER BY Introducer) AS rownum, Introducer, BDM, TargetDate FROM Reporting.dbo.IntroducerDailyRef

  • Row_number function not starting from 1November 30

    I have two row_number functions in my select statement both has same columns and condition for partition and order by, except one column in order by which is being ordered by DESC.below is function : row_number() over (partition by event_cd order by

Copyright (C) 2019 wisumpire.com, All Rights Reserved. webmaster#wisumpire.com 14 q. 0.455 s.