Visualize SQL | Report Formatting(part 1)

# Beginning
This technique is based on SQL Puzzle and Answers # 62 REPORT FORMATTING
It is better with 『SQL Puzzle and Answers』 to read this article.

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

This puzzle is very simple

There is a table of name list
and display names like two across, three across

・・・ it is easy to say 
but we do not do this kind of thing with SQL

The author, Mr Joe Celko, also says

～～～～～～～～～～～～～～～～～～～～～～～～～～～
**quoted from the book**

SQL is a data retrieval language and not a report writer
Unfortunately, people do not seem to know this 
and are always trying to do things for which
SQL was not intended ・・・・
～～～～～～～～～～～～～～～～～～～～～～～～～～～

The person who came up with such a puzzle is crazy ・・
but the people who thought about various answers to this puzzle 
are also crazy

`crazy means I do respect them`

this puzzle is not practical but personally ・・・
I think it's an interesting puzzle

Let's start
We are going to display names two across this time

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

▼ Output format is like image

![re_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/787ade07-12f1-568c-6e9e-344490225fd4.png)

# Table and data
```SQL:SQL
CREATE TABLE Names(
	name VARCHAR(15) NOT NULL PRIMARY KEY
);

INSERT INTO Names
VALUES ('Al'), 
	   ('Ben'),
	   ('Charlie'),
       ('David'),
	   ('Ed'),
	   ('Frank'),
       ('Greg'),
	   ('Howard'),
	   ('Ida'),
       ('Joe'),
	   ('Ken'),
	   ('Larry'),
       ('Mike'),
	   ('Neal');
```

▼ Output

![re_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/387b04ab-2118-4047-0c24-1467405d9009.png)

# Creating original data using LEFT JOIN
```SQL:SQL
SELECT N1.name, N2.name
FROM Names AS N1 
LEFT JOIN Names AS N2 ON N1.name < N2.name
```

▼ Output(left side of image)

Left side is original output, but rearranged it like right side of image
Because it is convenient to explain to puzzle

![re_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/9ea5c20c-da83-d23f-bc13-ef8460b82d05.png)

▼ Output(It is rearranged)

![re_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/e5bb07f7-d19a-fee4-075e-db542518db98.png)


Pay attention to the join condition of LEFT JOIN
　⇒　**N1.name < N2.name**


When creating all combinations,  **all names after myself are joined**
　⇒　(e.g) myself is N1.name, after myself is ・・・
　⇒　myself is A, after myself are B,C,D,E,F,G ・・・
　⇒　myself is B, after myself are C,D,E,F,G,H ・・・
　⇒　myself is C, after myself are D,E,F,G,H,I ・・・


What is 『N1.name < N2.name』 doing?
　⇒　**Exclude the same combination** 
　⇒　Comparison operator is 『<』 not 『<=』
　⇒　**this also exclude combinations with the same name as myself** 
　⇒　No combination like 『Grreg  Greg』 is needed



This original data is created by self-joining Names table
Names table has one column(name) and self-joining the same table
**This original data is already in the form of 『names two across』**
Some row of this data will be the data we want to take out




![re_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/9f53dd34-fc59-aca9-8535-2fdb01213dc9.png)

# Creating another combination
```SQL:SQL
SELECT A.name,B.name
FROM Names AS A 
INNER JOIN Names AS B ON A.name <= B.name
```

▼ Output

Join condition operator is 『<=』
so including combinations with the same name as myself
　⇒　`bold letters` in the image below

why operator is 『<=』 this time?
We need to check these number, it will be needed later

How many combinations of names start with A?
How many combinations of names start with B?
How many combinations of names start with C?
　・　　・　　・　　・　　・　　・　　・　　・
　・　　・　　・　　・　　・　　・　　・　　・
How many combinations of names start with N?



![re_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/b1a8c34b-642a-0777-3b85-a875745fc83e.png)



# Edit another combination 1st
```SQL:SQL
SELECT
  A.name,
  COUNT(B.name),
  (SELECT COUNT(*) FROM Names)
FROM Names AS A 
INNER JOIN Names AS B ON A.name <= B.name
GROUP BY A.name
```

Count number of data

COUNT(B.name)
　⇒　The number of A.name and B.name are the same
　⇒　check above image


SELECT COUNT(＊) FROM Names
　⇒　The number of names in the Names table
　⇒　AI,Ben,Charlie,David,Ed,・・・・Mike,Neal
　⇒　14

▼ Output

![re_7.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/0d34c8cb-7185-c397-0791-a75594d8ea4a.png)


# Edit another combination 2nd
```SQL:SQL
SELECT
  A.name,
  COUNT(B.name),
  (SELECT COUNT(*) FROM Names),
  MOD(COUNT(B.name), 2),
  (SELECT MOD(COUNT(*), 2) FROM Names)
FROM Names AS A 
INNER JOIN Names AS B ON A.name <= B.name
GROUP BY A.name
```

▼ Output

Add MOD(COUNT(B.name), 2) and (SELECT MOD(COUNT(＊), 2) FROM Names)
in SELECT statement
　⇒　MOD(●●●,2)
　⇒　remainder of (●●● ÷ 2)

SELECT MOD(COUNT(＊), 2) FROM Names
　⇒　COUNT(＊) is 14
　⇒　remainder of (14 ÷ 2) is 0
　⇒　all rows are 0

MOD(COUNT(B.name), 2)
　⇒　remainder of (14 ÷ 2) is 0 | AI
　⇒　remainder of (13 ÷ 2) is 1 | Ben
　⇒　remainder of (12 ÷ 2) is 0 | Charlie
　⇒　remainder of (11 ÷ 2) is 1 | David
　　　・　　・　　・　　・　　・
　　　・　　・　　・　　・　　・
　　　・　　・　　・　　・　　・
　⇒　remainder of (2 ÷ 2) is 0 | Mike
　⇒　remainder of (1 ÷ 2) is 1 | Neal


![re_8.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/e11ad467-b823-b0b2-35fb-df065d8fb187.png)

# Edit another combination 3rd
```SQL:SQL
SELECT
  A.name,
  COUNT(B.name),
  (SELECT COUNT(*) FROM Names),
  MOD(COUNT(B.name), 2),
  (SELECT MOD(COUNT(*), 2) FROM Names)
FROM Names AS A 
INNER JOIN Names AS B ON A.name <= B.name
GROUP BY A.name

HAVING MOD(COUNT(B.name), 2) = (SELECT MOD(COUNT(*), 2) FROM Names)
```

▼ Output

Add HAVING condition ・・・
taking out rows that fit below condition
　⇒　MOD(COUNT(B.name), 2) = (SELECT MOD(COUNT(*), 2) FROM Names)
　⇒　MOD(COUNT(B.name), 2) = 0

As I mentioned before ・・・

We are going to display names two across
The original data use the same 『Names table』 to LEFT JOIN
So `『two across name』 are already lined vertically`
　⇒　**Needed to extract specific rows from this**
　⇒　names which output of MOD(COUNT(B.name), 2) is 0
　⇒　A.name that has an even number of name combinations

This is done by
`HAVING MOD(COUNT(B.name), 2) = (SELECT MOD(COUNT(*), 2) FROM Names)`


![re_9.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/4e51076d-fe4f-8042-3d96-c9b02a587180.png)

We are ready to retrieve the data
Why we need to find those names? we can see next

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
**note**

Since Names table has 14 data
"SELECT MOD(COUNT(*), 2) FROM Names" returns 0 this time

If Names table has 11 or 23 data (add number)
we needs a little change
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

# Edit the original data
```SQL:SQL
SELECT N1.name, N2.name
FROM Names AS N1 
LEFT JOIN Names AS N2 ON N1.name < N2.name
WHERE N1.name IN (
  SELECT A.name FROM Names AS A 
  INNER JOIN Names AS B ON A.name <= B.name
  GROUP BY A.name
  HAVING MOD(COUNT(B.name), 2) = (SELECT MOD(COUNT(*), 2) FROM Names)
)
```

Add WHERE clause to the original data created using LEFT JOIN
Then use the SQL that can get names in the previous step
as a subquery in the WHERE clause

In the end, we will retrieve the names of red characters in the image below
First, taking out data with the same name(N1.name) as the name
returned from the subquery


![re_10.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/e579daec-415a-6041-bc19-c74d9947ec02.png)

-----

▼ Output


![re_11.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/86331e07-c7b5-5ad7-e55d-a30fc57e92c6.png)

Taking out the names of red letters next


# Answer SQL
```SQL:SQL
SELECT N1.name, MIN(N2.name) AS name2
FROM Names AS N1 
LEFT JOIN Names AS N2 ON N1.name < N2.name
WHERE N1.name IN (
	SELECT A.name FROM Names AS A 
	INNER JOIN Names AS B ON A.name <= B.name
	GROUP BY A.name
	HAVING MOD(COUNT(B.name), 2) = (SELECT MOD(COUNT(*), 2) FROM Names)
)
GROUP BY N1.name
```

▼ Output(names two across)

![re_12.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/495c6d3e-7270-a7d2-f472-9ad52a1e4874.png)


The point of this puzzle is the subquery in the WHERE clause
This subquery identifies the row of the data we want to pick from the original data

In the end, GROUP BY with N1.name to get the first name
The second name(N2.name) is not an aggregate variable, so get it with MIN(N2.name)
　⇒　Since MIN function is used, the first name(red letters) can be taken


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