# Ranking the rows

I was asked a few days to write a SQL that would retrieve the one but latest row. Take as an example a few rows below: we have a several functions with their min salary. The question is: which function earns the one but highest minumum salary. In the rows below, we have minimum salary 20000, 15000, 3000 and 8200. The outcome would be the row where the minimum salary 15000 is found as we should disregard the highest number (here 20000).

JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
20000
40000
15000
30000
3000
6000
FI_MGR Finance Manager
8200
16000

To this problem, we have two solutions:
One solution is:

```SELECT rownum, min_salary from
(select min_salary from jobs order by min_salary desc)A ;
```

Here, we order the table first, then return the rows with the rownumber. This could then be filtered on rownumber=2.
The second solution is:

```SELECT min_salary,
RANK() OVER (ORDER BY min_salary desc) "rank"
FROM   jobs;
```

This makes use of the so-called analytical functions. It leads to the same result: after a filter on RANK()=2, we have the desired row.