When it comes to working with large datasets in SQL Server, one common task is finding the highest or lowest values. However, what if we need to find the fifth highest salary in a given table? This can be a bit trickier to achieve, but fear not – with the right query, it can be done easily.
First, let's set the stage with a sample table of employee salaries. We'll call this table "employees" and it will contain the following columns: id, name, salary.
| id | name | salary |
|----|------|--------|
| 1 | John | 50000 |
| 2 | Jane | 60000 |
| 3 | Mark | 40000 |
| 4 | Mary | 75000 |
| 5 | Alex | 55000 |
| 6 | Lisa | 65000 |
| 7 | Eric | 45000 |
| 8 | Sarah| 80000 |
| 9 | Mike | 70000 |
| 10 | Anna | 85000 |
Now, let's say we need to find the fifth highest salary in this table. We could use the "TOP" keyword with the "ORDER BY" clause to specify the number of rows we want to return and the column we want to order them by. In this case, we want to order by the "salary" column in descending order (highest to lowest).
Our query would look like this:
```sql
SELECT TOP 1 salary
FROM employees
ORDER BY salary DESC
```
This would return the highest salary in the table, which is 85000. But we want the fifth highest salary, not the first. So, we need to modify our query.
To get the fifth highest salary, we need to use the "OFFSET" keyword. This allows us to specify a starting point and a number of rows to skip before returning the results. Since we want to skip the first four rows (which contain the top four salaries), we'll use an offset of 4. Our query now looks like this:
```sql
SELECT TOP 1 salary
FROM employees
ORDER BY salary DESC
OFFSET 4 ROWS
```
This will return the fifth highest salary, which is 65000. But what if there are duplicate salaries in the top four? For example, if there were two employees with a salary of 80000, our previous query would still return 65000 as the fifth highest salary.
To account for this, we can use the "DISTINCT" keyword to eliminate duplicate values. Our final query looks like this:
```sql
SELECT DISTINCT TOP 1 salary
FROM employees
ORDER BY salary DESC
OFFSET 4 ROWS
```
This will return the correct result of 70000 as the fifth highest salary.
In summary, to find the fifth highest salary in a single SQL query, we use the "TOP" and "OFFSET" keywords along with the "ORDER BY" clause. By also using the "DISTINCT" keyword, we can ensure that duplicate values do not affect our results.
In conclusion, finding the fifth highest salary in SQL Server can be achieved with a single query by using the right combination of keywords and clauses. This is just one example of the powerful capabilities of SQL when working with large datasets. With a bit of
