## 175. Combine Two Tables

Question:

Solution:

```select
p.firstname,
p.lastname,
a.city,
a.state
from person p
on p.personid = a.personid

```

## 176. Second Highest Salary

Question:

Solution:

```select
case
when count(*) >0 then SecondHighestSalary
else null
end as SecondHighestSalary
from(
select
distinct salary as SecondHighestSalary
from(
select
salary,
dense_rank() over(order by salary desc) as r
from employee
) s
where r = 2
) s1
```

Notes:

1. The key is to use the dense_rank() function rather than using the rank() function. dense_rank() is able to address the situation that employees can have the same salary amount. dense_rank() will return the same rank when there is a tie between employee A and B.
2. The CASE WHEN statement will return NULL when there is ONLY one employee in the dataset and output the 2nd highest salary when there are >=2 employees.

## 177. Nth Highest Salary

Question:

Solution:

```CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
select
case
when count(*) is not null then salary
else null
end as salary
from(
select
salary,
dense_rank() over(order by salary desc) r
from employee
) s
where r = N
);
END
```

## 178. Rank Scores

Question:

Solution:

```select
score,
r as "Rank"
from(
select
score,
dense_rank() over(order by score desc) as r
from scores
) a
order by 2
```

## 180. Consecutive Numbers

Question:

Solution:

```select
distinct Num as ConsecutiveNums
from(
select
Id,
Num,
case
when lag_num_1 = num and lag_num_2 = num then 1
else 0
end as consec_num
from(
select
Id,
Num,
lag(Num,1) over(order by Id) as lag_num_1,
lag(Num,2) over(order by Id) as lag_num_2
from logs
) l
) l2
where consec_num =1
```