Oracle query to retrieve the previous value of a related row in a single table

Question

I have a Student table that has the name and ratings of the year.

Name    Year    Rating

Ram    2016      10
Sam    2016      9
Ram    2014      8
Sam    2012      7

I need to find the previous employee rating, which could have been last year or several years ago.

The query should return below results

Name    Cur_rating_year_2016    Prev_rating
Ram               10            8
Sam                9            7

Below is a script to insert and create

Create table Student (name varchar2(10), year number, rating number  );
insert into student values('Ram' ,2016 ,10);
insert into student values('Sam' ,2016 ,9);
insert into student values('Sam' ,2012 ,7);
insert into student values('Ram' ,2014 ,8);

Is there a way to achieve this using a select query?

Answer 1

Use the analytic function LAG https://docs.oracle.com/database/122/SQLRF/LAG.htm#SQLRF00652

LAG - . . , , , LAG .

offset , . , 1. , . , .

SELECT stud_name AS name, 
       r_year AS year,
       r_value AS rating,
       lag(r_value, 1, NULL) OVER(PARTITION BY stud_name ORDER BY r_year) AS prev_rating
  FROM stud_r
 ORDER BY stud_name;

Answer 2

:

SELECT A.NAME,A.RATING,B.RATING  FROM 
    STUDENTS A INNER JOIN STUDENTS B 
    ON A.NAME=B.NAME  
    WHERE A.YEAR='2016' AND B.YEAR<>'2016'
    ORDER BY A.NAME ASC