+ Reply to Thread
Page 4 of 6 FirstFirst ... 2 3 4 5 6 LastLast
Results 31 to 40 of 56

how to do row number query

  1. Re: how to do row number query

    here is the definition of rrn() function on iSeries DB2,

    The RRN function returns the relative record number of a row.

    and here is the example


    Return the relative record number and employee name from table
    EMPLOYEE for those employees in department 20.
    SELECT RRN(EMPLOYEE), LASTNAME
    FROM EMPLOYEE
    WHERE DEPTNO = 20

    so we don't care about order by, we want to know the row number of
    this table meet the criteria, is DB2 UDB on windows ( i am using db2
    v8.1) has same function? thanks

  2. Re: how to do row number query

    xixi wrote:

    > here is the definition of rrn() function on iSeries DB2,
    >
    > The RRN function returns the relative record number of a row.


    What is the "relative record number"? Relative to what?

    > and here is the example
    >
    > Return the relative record number and employee name from table
    > EMPLOYEE for those employees in department 20.
    > SELECT RRN(EMPLOYEE), LASTNAME
    > FROM EMPLOYEE
    > WHERE DEPTNO = 20
    >
    > so we don't care about order by, we want to know the row number of
    > this table meet the criteria, is DB2 UDB on windows ( i am using db2
    > v8.1) has same function? thanks


    I really don't know exactly what you want to have in the result, but you
    could easily do this:

    SELECT rrn, lastname
    FROM ( SELECT row_number() over (), lastname, deptno
    FROM employee ) AS t(rrn, lastname, deptno)
    WHERE deptno = 20


    If you want to get a "row number" that indicates the relative position of
    the physical row with respect to the beginning of the table, then you
    should be aware that those numbers might change quite easily on
    insert/update/delete operations, reorgs, ...

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena

  3. Re: how to do row number query

    xixi wrote:

    > here is the definition of rrn() function on iSeries DB2,
    >
    > The RRN function returns the relative record number of a row.


    What is the "relative record number"? Relative to what?

    > and here is the example
    >
    > Return the relative record number and employee name from table
    > EMPLOYEE for those employees in department 20.
    > SELECT RRN(EMPLOYEE), LASTNAME
    > FROM EMPLOYEE
    > WHERE DEPTNO = 20
    >
    > so we don't care about order by, we want to know the row number of
    > this table meet the criteria, is DB2 UDB on windows ( i am using db2
    > v8.1) has same function? thanks


    I really don't know exactly what you want to have in the result, but you
    could easily do this:

    SELECT rrn, lastname
    FROM ( SELECT row_number() over (), lastname, deptno
    FROM employee ) AS t(rrn, lastname, deptno)
    WHERE deptno = 20


    If you want to get a "row number" that indicates the relative position of
    the physical row with respect to the beginning of the table, then you
    should be aware that those numbers might change quite easily on
    insert/update/delete operations, reorgs, ...

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena

  4. Re: how to do row number query

    xixi wrote:

    > here is the definition of rrn() function on iSeries DB2,
    >
    > The RRN function returns the relative record number of a row.


    What is the "relative record number"? Relative to what?

    > and here is the example
    >
    > Return the relative record number and employee name from table
    > EMPLOYEE for those employees in department 20.
    > SELECT RRN(EMPLOYEE), LASTNAME
    > FROM EMPLOYEE
    > WHERE DEPTNO = 20
    >
    > so we don't care about order by, we want to know the row number of
    > this table meet the criteria, is DB2 UDB on windows ( i am using db2
    > v8.1) has same function? thanks


    I really don't know exactly what you want to have in the result, but you
    could easily do this:

    SELECT rrn, lastname
    FROM ( SELECT row_number() over (), lastname, deptno
    FROM employee ) AS t(rrn, lastname, deptno)
    WHERE deptno = 20


    If you want to get a "row number" that indicates the relative position of
    the physical row with respect to the beginning of the table, then you
    should be aware that those numbers might change quite easily on
    insert/update/delete operations, reorgs, ...

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena

  5. Re: how to do row number query

    xixi wrote:

    > here is the definition of rrn() function on iSeries DB2,
    >
    > The RRN function returns the relative record number of a row.


    What is the "relative record number"? Relative to what?

    > and here is the example
    >
    > Return the relative record number and employee name from table
    > EMPLOYEE for those employees in department 20.
    > SELECT RRN(EMPLOYEE), LASTNAME
    > FROM EMPLOYEE
    > WHERE DEPTNO = 20
    >
    > so we don't care about order by, we want to know the row number of
    > this table meet the criteria, is DB2 UDB on windows ( i am using db2
    > v8.1) has same function? thanks


    I really don't know exactly what you want to have in the result, but you
    could easily do this:

    SELECT rrn, lastname
    FROM ( SELECT row_number() over (), lastname, deptno
    FROM employee ) AS t(rrn, lastname, deptno)
    WHERE deptno = 20


    If you want to get a "row number" that indicates the relative position of
    the physical row with respect to the beginning of the table, then you
    should be aware that those numbers might change quite easily on
    insert/update/delete operations, reorgs, ...

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena

  6. Re: how to do row number query

    xixi wrote:

    > here is the definition of rrn() function on iSeries DB2,
    >
    > The RRN function returns the relative record number of a row.


    What is the "relative record number"? Relative to what?

    > and here is the example
    >
    > Return the relative record number and employee name from table
    > EMPLOYEE for those employees in department 20.
    > SELECT RRN(EMPLOYEE), LASTNAME
    > FROM EMPLOYEE
    > WHERE DEPTNO = 20
    >
    > so we don't care about order by, we want to know the row number of
    > this table meet the criteria, is DB2 UDB on windows ( i am using db2
    > v8.1) has same function? thanks


    I really don't know exactly what you want to have in the result, but you
    could easily do this:

    SELECT rrn, lastname
    FROM ( SELECT row_number() over (), lastname, deptno
    FROM employee ) AS t(rrn, lastname, deptno)
    WHERE deptno = 20


    If you want to get a "row number" that indicates the relative position of
    the physical row with respect to the beginning of the table, then you
    should be aware that those numbers might change quite easily on
    insert/update/delete operations, reorgs, ...

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena

  7. Re: how to do row number query

    Relative record numbers identify the positions of the records relative
    to
    the beginning of the file. For example, the relative record
    numbers of
    the first, fifth, and seventh records are 1, 5, and 7,
    respectively. so i don't select the relative record number, i need to
    use relative record number in where clause to retrieve the row like
    this sql worked on AS400:

    select * from table where rrn(table)=1, please help

  8. Re: how to do row number query

    Relative record numbers identify the positions of the records relative
    to
    the beginning of the file. For example, the relative record
    numbers of
    the first, fifth, and seventh records are 1, 5, and 7,
    respectively. so i don't select the relative record number, i need to
    use relative record number in where clause to retrieve the row like
    this sql worked on AS400:

    select * from table where rrn(table)=1, please help

  9. Re: how to do row number query

    Relative record numbers identify the positions of the records relative
    to
    the beginning of the file. For example, the relative record
    numbers of
    the first, fifth, and seventh records are 1, 5, and 7,
    respectively. so i don't select the relative record number, i need to
    use relative record number in where clause to retrieve the row like
    this sql worked on AS400:

    select * from table where rrn(table)=1, please help

  10. Re: how to do row number query

    Relative record numbers identify the positions of the records relative
    to
    the beginning of the file. For example, the relative record
    numbers of
    the first, fifth, and seventh records are 1, 5, and 7,
    respectively. so i don't select the relative record number, i need to
    use relative record number in where clause to retrieve the row like
    this sql worked on AS400:

    select * from table where rrn(table)=1, please help

+ Reply to Thread
Page 4 of 6 FirstFirst ... 2 3 4 5 6 LastLast