Query Select Help

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • matias.cornejo@gmail.com
    #1

    Query Select Help

    I have a table with 1.000.000 registers and I want to find the
    register in a position 25.000. I don't want to use a cursor, how can I
    do it in other way?

    Thenks in advance

    Tags: None
    • Lennart
      #2
      Re: Query Select Help

      matias.cornejo@ gmail.com wrote:
      I have a table with 1.000.000 registers and I want to find the
      register in a position 25.000. I don't want to use a cursor, how can I
      do it in other way?
      >
      Thenks in advance
      >
      select * from (
      select T.*, row_number() over (order by <position>) as rn
      from T
      ) X where rn = 25000


      /Lennart

        Comment

        • Knut Stolze
          #3
          Re: Query Select Help

          matias.cornejo@ gmail.com wrote:
          I have a table with 1.000.000 registers and I want to find the
          register in a position 25.000. I don't want to use a cursor, how can I
          do it in other way?
          Tables contain sets of rows. Sets are - per definition - not ordered. So
          there is no 1st, 2nd, or 25000th row. So you have to define a sort
          criteria and then you can use Lennart's query, for example.

          --
          Knut Stolze
          DB2 z/OS Utilities Development
          IBM Germany

            Comment

            • matias.cornejo@gmail.com
              #4
              Re: Query Select Help

              On May 15, 6:02 am, Knut Stolze <sto...@de.ibm. comwrote:
              matias.corn...@ gmail.com wrote:
              I have a table with 1.000.000 registers and I want to find the
              register in a position 25.000. I don't want to use a cursor, how can I
              do it in other way?
              >
              Tables contain sets of rows. Sets are - per definition - not ordered. So
              there is no 1st, 2nd, or 25000th row. So you have to define a sort
              criteria and then you can use Lennart's query, for example.
              >
              --
              Knut Stolze
              DB2 z/OS Utilities Development
              IBM Germany
              Thaks to all.

              I did the next query:

              select rut_cliente FROM (SELECT ROW_NUMBER() OVER (ORDER BY
              RUT_CLIENTE) AS POSITION, RUT_CLIENTE FROM TABLE_NAME ORDER BY
              RUT_CLIENTE) AS x WHERE POSITION = 2



                Comment

                • Serge Rielau
                  #5
                  Re: Query Select Help

                  matias.cornejo@ gmail.com wrote:
                  select rut_cliente FROM (SELECT ROW_NUMBER() OVER (ORDER BY
                  RUT_CLIENTE) AS POSITION, RUT_CLIENTE FROM TABLE_NAME ORDER BY
                  RUT_CLIENTE) AS x WHERE POSITION = 2
                  FYI: You don't need the ORDER BY on the query itself. The one in
                  ROW_NUMBER() OVER() is sufficient.
                  Won't change the plan though. Just less typing
                  --
                  Serge Rielau
                  DB2 Solutions Development
                  IBM Toronto Lab

                    Comment

                    Previous template Next
                    Working...