This content has been marked as final. Show 5 replies
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.
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:
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?
Edited by: firstname.lastname@example.org on Feb 25, 2010 2:10 PM
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 andnot 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.
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