5 Replies Latest reply on Mar 1, 2010 2:34 PM by user1175345

    function:   row_number

    user1175345
      Hi Everyone,

      Hope all is well....

      I'd like to show just one row for a student.

      Essentially, I'd like to group the rows by student id, sorted by primary advisor ind, so that i can take the last row.

      test data:
      =======
      111111 N
      111111 N
      111111 Y

      222222 N

      just show:
      ========
      111111 Y
      222222 N

      DISCOVERED row number function to assign a row number to each row in a set (by student id)


      row number function accomplishes:
      111111 N 1
      111111 N 2
      111111 Y 3

      222222 N 1


      so i'd like to create a condition that compares the row_number to the max row number for the group

      for this results set
      111111 Y 3
      222222 N 1


      SO CREATED 2 FUNCTIONS

      1)
      calc_row_number:
      ROW_NUMBER() OVER(PARTITION BY Academic Study.Id ORDER BY Academic Study.Id||Primary Advisor Indicator )

      2)

      calc_max_row:
      MAX(Calc_row_number) OVER(PARTITION BY Academic Study.Id )

      3) then created a condition:

      calc_row_number = calc_max_row

      it's very mystifying that the results set mostly performs the condition as expected
      where the row that is selected the detail row number = the max row number for ex: 12 = 12

      however,
      there are some rows that were selected that the detail row number is different then max
      for ex: 1 = 2

      and i don't know why that is happening....
      do you have any ideas or advice, tx, sandra

      Edited by: mytfein@netzero.net on Feb 25, 2010 7:27 AM

      Edited by: mytfein@netzero.net on Feb 25, 2010 7:29 AM
        • 1. Re: function:   row_number
          Rod West
          Hi,

          You don't need to use analytic functions to do this, you should just use an aggregate MAX function. So if you want to show the last value of the student_flag column (where the last record is defined by the adviser_id) then use:

          MAX(student_flag) KEEP (DENSE_RANK LAST ORDER BY adviser_id)

          If you have student_id in the worksheet then the report will be grouped by student and so you will only get one record for each student.

          Rod West
          • 2. Re: function:   row_number
            user1175345
            Hi Rod,

            thx for writing... just notice your response...

            a) can you explain what dense_rank last does? how is it diff than just regular MAX?

            does max...dense rank, act like a max with a gROUP by clause in classical sql.
            in that you group by student id, and take the max flag value?

            b) i have 2 fields:
            student_id
            primary_advisor_ind (whether a faculty member was assigned to be their primary advisor)

            so not sure what you mean by advisor_id in the max...dense rank function

            c) i could have a few rows with Y, and how can i be sure that dense rank will give me the last of the Y's and
            not the first of the Y?

            That's why i like row number, bec it can give me control to be assured that i am truly getting the last record
            of the student group?

            tx, sandra

            Edited by: mytfein@netzero.net on Feb 25, 2010 2:10 PM
            • 3. Re: function:   row_number
              Rod West
              Hi,
              a) can you explain what dense_rank last does? how is it diff than just regular MAX?
              The MAX() KEEP (DENSE_RANK LAST/FIRST ORDER BY ) syntax is a variant of the MAX() group function. What it does is order by the rows within the group using the order by column then take the last or first row(s). It will then give you the MAX of the column in those last/first rows. Therefore the MAX is only used if there is more than one row with the same value in the order by column. So if your order by column is unique within the group then there will be only one last row and so the max will just take the column value from that last row. You could equally well use MIN.
              b) i have 2 fields:student_id, primary_advisor_ind (whether a faculty member was assigned to be their primary advisor)
              You need a column or expression to define what is your last row within the group. You used primary_advisor_ind in your order by so I assumed that was how you were sorting your rows. Normally, I would expect to see a date field or sequence field in this column.
              c) i could have a few rows with Y, and how can i be sure that dense rank will give me the last of the Y's and
              not the first of the Y?
              The MAX() KEEP (DENSE_RANK LAST/FIRST ORDER BY ) sorts the rows first. If all the rows in your group (ie. by student id) have the same order by column then you will just get the MAX of the column. But if you order by is different for each row in your group then you will get the column value from the last row.

              Rod West
              • 4. Re: function:   row_number
                user1175345
                Hi Rod,

                Thx for writing back... sorry for the delay on my part, Friday we had a blizzard....

                will post a similar/diff quest. shortly.... tx,


                tx for your help, sandra
                • 5. Re: function:   row_number
                  user1175345
                  Hi Rod,

                  If you have a few minutes, posted a related question, here

                  MAX function

                  tx very much, sandra