Visualize SQL | Find The Last Two Salaries(part 2) | CROSS JOIN and MAX

# Beginning
This technique is based on SQL Puzzle and Answers # 15 Find The Last Two Salaries
It is better with 『SQL Puzzle and Answers』 to read this article.

Because it contains the idea of algorithm and
You can check what each quiz is intended for ・・・

Let's start !!

Employee name, promotion date and salary are added in one line
Some employees have been promoted a few times and Someone has not
　⇒　check the Salaries table

`This quiz is to get current and previous salary information on one line`
The format to display is below


![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/1be50ede-635a-56f2-0fc0-58eab3774c9a.png)



**I have confirmed that it works with PostgreSQL**

# Salaries table
```SQL:SQL
CREATE TABLE Salaries (
	emp_name CHAR(10) NOT NULL,
	sal_date DATE NOT NULL,
	sal_amt DECIMAL(8,2) NOT NULL,
	PRIMARY KEY (emp_name, sal_date)
)

INSERT INTO Salaries
VALUES ('Tom',   '1996-06-20', 500.00),
       ('Tom',   '1996-08-20', 700.00),
       ('Tom',   '1996-10-20', 800.00),
       ('Tom',   '1996-12-20', 900.00),
       ('Dick',  '1996-06-20', 500.00),
       ('Harry', '1996-07-20', 500.00),
       ('Harry', '1996-09-20', 700.00);
```

▼ Output


![rep_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/b5a2ab3b-366f-78df-f63a-00c4812ab10e.png)



# Creating original data using CROSS JOIN
```SQL:SQL
SELECT S0.*, S1.*
FROM Salaries AS S0, Salaries AS S1
WHERE S0.emp_name = S1.emp_name
```

▼ (right side of image)

There are three employees data and seven records in the Salaries table
`Creating all combinations of data using CROSS JOIN`
and taking out the combination of the same employees

Tom has four data : 4 x 4 = 16
Marry has two data : 2 x 2 = 4
Dick has one data : 1 x 1 = 1


Below is the image of the original data
**Taking out current salary information from S0 side**
**Taking out previous salary information from S1 side**

![rep_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/c4aec389-097b-7326-f593-e4321a52e365.png)

----


![rep_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/6cfe8e32-3118-93a6-d313-c312f03b35ae.png)


Tom's case, current promotion can be taken by MAX(S0.sal_date) ⇒ 1996-12-20
previous promotion is 1996-10-20 by doing the same like above image

We are going to do these processes with SQL

# Add Subqueries in SELECT statement 1st
```SQL:SQL
SELECT S0.*, S1.sal_date,
 (SELECT array_agg(S2.sal_date) FROM Salaries AS S2 WHERE S0.emp_name = S2.emp_name),
 (SELECT array_agg(S3.sal_date) FROM Salaries AS S3 WHERE S0.emp_name = S3.emp_name 
                                                      AND S0.sal_date > S3.sal_date)
FROM Salaries AS S0, Salaries AS S1
WHERE S0.emp_name = S1.emp_name
```

array_agg(S2.sal_date)
　⇒　self-joining Slaries table
　⇒　condition is the same employee | `S0.emp_name = S2.emp_name`
　⇒　taking out promotions of the same employee existing in S0 from S2
　⇒　**a set of promotions each employee has**
　⇒　taking out current promotion from this set


array_agg(S3.sal_date)
　⇒　add one more condition which is `S0.sal_date > S3.sal_date`
　⇒　taking out promotions of before S0.sal_date from S3
　⇒　**a set of promotions before S0.sal_date each employee has**
　⇒　taking out previous promotion from this set

![rep_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/7ab2ea2a-e1aa-7a65-99eb-d438bd149264.png)


# Add Subqueries in SELECT statement 2nd
```SQL:SQL
SELECT S0.*, S1.sal_date,
 (SELECT array_agg(S2.sal_date) FROM Salaries AS S2 WHERE S0.emp_name = S2.emp_name),
 (SELECT MAX(S2.sal_date) FROM Salaries AS S2 WHERE S0.emp_name = S2.emp_name),

 (SELECT array_agg(S3.sal_date) FROM Salaries AS S3 WHERE S0.emp_name = S3.emp_name 
                                                    AND S0.sal_date > S3.sal_date),
 (SELECT MAX(S3.sal_date) FROM Salaries AS S3 WHERE S0.emp_name = S3.emp_name 
                                                    AND S0.sal_date > S3.sal_date)
FROM Salaries AS S0, Salaries AS S1
WHERE S0.emp_name = S1.emp_name
```

MAX(S2.sal_date)
　⇒　the latest day in a set of promotions each employee has
　⇒　current promotion

MAX(S3.sal_date)
　⇒　the latest day in a set of promotions before S0.sal_date each employee has
　⇒　previous promotion
　⇒　there are a few promotions ・・・ which date ? check image

We are going to take out the row 
that has a combination of MAX(S2.sal_date) and MAX(S3.sal_date)


![rep_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/853287f6-5311-7f73-f090-ca7b6a5f00f4.png)

# Use MAX(S2.sal_date) MAX(S3.sal_date) in WHERE clause
```SQL:SQL
SELECT S0.*, S1.sal_date,S1.sal_amt,
 (SELECT MAX(S2.sal_date) FROM Salaries AS S2 WHERE S0.emp_name = S2.emp_name),
 (SELECT MAX(S3.sal_date) FROM Salaries AS S3 WHERE S0.emp_name = S3.emp_name 
                                                    AND S0.sal_date > S3.sal_date)
FROM Salaries AS S0, Salaries AS S1
WHERE S0.emp_name = S1.emp_name
  AND S0.sal_date = (SELECT MAX(S2.sal_date) FROM Salaries AS S2 
                     WHERE S0.emp_name = S2.emp_name)
  AND S1.sal_date = (SELECT MAX(S3.sal_date) FROM Salaries AS S3 
                     WHERE S0.emp_name = S3.emp_name AND S0.sal_date > S3.sal_date)
```

▼ 出力 (Dick does not show)

|emp_name|S0.sal_date|sal_amt|S1.sal_date|sal_amt|MAX(S2.sal_date)|MAX(S3.sal_date)|
|---|---|---|---|---|---|---|
|Tom|1996-12-20|900|1996-10-20|800|1996-12-20|1996-10-20|
|Harry|1996-09-20|700|1996-07-20|500|1996-09-20|1996-07-20|

I mentioned earlier ・・・
**Taking out current salary information from S0 side**
**Taking out previous salary information from S1 side**

Add conditions in WHERE clause
　⇒ S0.sal_date is current promotion
　　 S0.sal_date = (SELECT MAX(S2.sal_date) ・・・  <--- ■ condition１
　⇒ S1.sal_date is previous promotion
　　 S1.sal_date = (SELECT MAX(S3.sal_date) ・・・  <--- ■ condition2

■ condition１：current promotion
　⇒　Since S0.sal_date has current promotion, choose the same date AS MAX(S2.sal_date)

■ condition2：previous promotion
　⇒　Since S1.sal_date has previous promotion, choose the same date AS MAX(S3.sal_date)


The row that fit condition 1 and condition 2 from the CROSS JOIN table data are below
There are Tom and Marry's row ・・・ but **No Dick's row**

Because `Dick does not has MAX(S3.sal_date) which is previous promotion`
『Dick's data』 does not fit the second condition
　⇒　S1.sal_date = (SELECT MAX(S3.sal_date) FROM Salaries AS S3 ・・・)
　⇒　`both conditions must be fit because of 『AND』 conditions`

**Dick's salary information must be taken in separately**


![rep_7.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/27d32431-bf1f-64ae-d23c-65d1549d5b5c.png)

------

Taking out Dick's salary information

Dick has one data in the Salaries table
『Data count』 after GROUP BY employee name is one
Tom and Harry have more than two 『Data count』

![rep_8.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/716cfff6-c5f0-f387-848f-2d01ba571d85.png)

# Answer SQL
```SQL:SQL
SELECT S0.emp_name, S0.sal_date, S0.sal_amt, S1.sal_date, S1.sal_amt
FROM Salaries AS S0, Salaries AS S1
WHERE S0.emp_name = S1.emp_name
  AND S0.sal_date = (SELECT MAX(S2.sal_date) FROM Salaries AS S2 
                     WHERE S0.emp_name = S2.emp_name)
  AND S1.sal_date = (SELECT MAX(S3.sal_date) FROM Salaries AS S3 
                     WHERE S0.emp_name = S3.emp_name AND S0.sal_date > S3.sal_date)
UNION ALL

SELECT S4.emp_name, MAX(S4.sal_date), MAX(S4.sal_amt),NULL, NULL
FROM Salaries AS S4
GROUP BY S4.emp_name
HAVING COUNT(*) = 1
```

▼ Output

Combine Dick's salary information using UNION ALL

|emp_name|S0.sal_date|S0.sal_amt|S1.sal_date|S1.sal_amt|
|---|---|---|---|---|
|Tom|1996-12-20|900|1996-10-20|800|
|Harry|1996-09-20|700|1996-07-20|500|
|Dick|1996-06-20|500|NULL|NULL|


# References 
[Joe Celko's SQL Puzzles and Answers](https://www.amazon.com/Puzzles-Answers-Kaufmann-Management-Systems/dp/0123735963)