Question: I have a table with below mentioned columns. I want to fetch the previous status of customer. Once customer id can have multiple entries

```
Customer_id   status     start_date    end_date   Active
1             Member      01-JAN-18    04-FEB-18   N
1             Explorist   05-FEB-18    30-APR-18   N
1             Globalist   01-MAY-18    31-DEC-99   Y

```

Desired output

```
Customer _id     Previous_status   end_date
1                Explorist          30-APR-18

```

Answer: 
Please try below query using QUALIFY keyword and ROW\_NUMBER():

```
SELECT a.* from table a 
QUALIFY ROW_NUMBER OVER(PARTITION BY customer_id order by start_date desc) = 2

```
If you are using Oracle DataBase then try below query using **ROW\_NUMBER()** function:Let's consider the table name is **customer**

```

    SELECT TEMP.CUSTOMER_ID
          ,TEMP.STATUS
          ,TEMP.START_DATE
          ,TEMP.END_DATE
          ,TEMP.ACTIVE 
          FROM(
            SELECT ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY CUSTOMER_ID ASC,START_DATE DESC) AS "ROW_NUM"
                  ,CUSTOMER_ID
                  ,STATUS
                  ,START_DATE
                  ,END_DATE
                  ,ACTIVE
                  FROM CUSTOMER) TEMP 
                      WHERE TEMP."ROW_NUM" = 2;

```