Over in Oracle (partition by ...) Analytic functions and window-opening functions

Suppose there is a table student

Name Score Inserttime (name: Name score: Score inserttime: Test time)

Zhang 320 2015-08-08

Lee 412 2015-09-01

Little Q 33 2015-09-03

Zhang 320 2015-09-08

Lee 412 2015-07-01

Little Q 25 2015-06-03

Now ask everyone to sort by the order of the test time, write out the Oracle statement

For:

Select Row_number () over (partition by name order by Inserttime) row_number,student.* from student

Explain:

Partition by first grouping by name, order by in order by Inserttiom

Results

Zhang 320 2015-08-08

Zhang 320 2015-09-08

Lee 412 2015-07-01

Lee 412 2015-09-01

Little Q 25 2015-06-03

Little Q 33 2015-09-03

This method can be applied flexibly. For example, take the latest data from each group or the first data

Select Row_number () over (partition by name order by Inserttime) row_number,student.* from student where row_number=1

Zhang 320 2015-08-08

Lee 412 2015-07-01

Little Q 25 2015-06-03

Have questions direct message first time reply

Over in Oracle (partition by ...) Analytic functions and window-opening functions