Visualize SQL | Report Formatting(part 2)

# 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.

It explains 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 ・・・・
～～～～～～～～～～～～～～～～～～～～～～～～～～～

In 『part 2』 the approach is different from 『part 1』
but the idea of finding the row we want to retrieve with the MOD function is the same

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

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

▼ Output format is like below image


![re_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/10a680a4-f505-6aae-2c59-cf3016194768.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/41c69d3e-ecc8-d402-bddf-6db685570014.png)

# Creating original data
```SQL:SQL
SELECT N1.name,N2.name
FROM Names AS N1 
INNER 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 this puzzle


![re_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/1c572cec-a2c6-5e5f-fca5-9fe1fc2f4ddd.png)



▼ Output(It is rearranged)

![re_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/f2192ea0-0668-22f9-c289-5a2cf2dccb4d.png)

Pay attention to the join condition of INNERJOIN
　⇒　**N1.name >= N2.name**

When creating all combinations, all names before myself are joined
　⇒　(e.g) myself is N1.name(Ben), before myself(N2.name) are AI and Ben
　⇒　myself is A, before myself is A
　⇒　myself is B, before myself are A,B
　⇒　myself is C, before myself are A,B,C
　　　・　　・　　・　　・
　　　・　　・　　・　　・
　⇒　myself is N, before myself are  A,B,C,D,E,D,G,H,I,J,K,L,M,N

What is 『N1.name >= N2.name』 doing?
　⇒　**Exclude the same combination**
　⇒　Comparison operator is **『<=』**
　⇒　including combinations with the same name as myself
　⇒　combinations like 『Grreg Greg』


![re_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/8b7eb023-2297-b5a2-f713-5e7bdda9bb62.png)



How to use the original data is different from 『part 1』
In 『part 1』, the original data is already in the form of 『names two across』

but this time, we display names five across, so it will not be the same
`we use only N1.name from the original data`
　⇒　the name displayed on the left(bold letters in below image)
　⇒　pick this name from Names AS N1

we are going to ・・・
**pick the first name on the left side**
**then join other names to the first name to become as 『names five across』**

below is the image of 『names five across』


![re_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/d2c24e6c-d158-2f9b-79e8-ae83b7f5537e.png)



# Edit original data 1st
```SQL:SQL
SELECT N1.name,COUNT(*),MOD(COUNT(*), 5)
FROM Names AS N1 
INNER JOIN Names AS N2 ON N1.name >= N2.name
GROUP BY N1.name
```

▼ Output

Add GROUP BY N1.name then count the number
　⇒ COUNT(＊)

MOD(COUNT(＊), 5)
　⇒ remainder of (COUNT(＊) ÷ 5)


![re_7.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/8220d656-aeba-723f-14fe-44a788b03d9e.png)


The names on the left side are Al, Frank, Ken
so pick rows where MOD(COUNT(＊), 5) is 1

# Edit original data 2nd
```SQL:SQL
SELECT N1.name,COUNT(*),MOD(COUNT(*), 5)
FROM Names AS N1 
INNER JOIN Names AS N2 ON N1.name >= N2.name
GROUP BY N1.name
HAVING MOD(COUNT(*), 5) = 1  -- add here
```

▼ Output

Add HAVING condition
　⇒　HAVING MOD(COUNT(＊), 5) = 1
　⇒　pick rows where MOD(COUNT(＊), 5) is 1
　⇒　output are the names that comes to the far left

![re_8.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/7a16a261-15b2-ab65-24d6-7d5e88487807.png)


# Pick the second name 1st
```SQL:SQL
SELECT N3.name,N4.name
FROM
(
	SELECT N1.name
	FROM Names AS N1 
	INNER JOIN Names AS N2 ON N1.name >= N2.name
	GROUP BY N1.name
	HAVING MOD(COUNT(*), 5) = 1
) AS N3(name) 
LEFT JOIN Names AS N4 ON N3.name < N4.name -- the second name
```

▼ Output

LEFT JOIN Names table to the SQL that retrieve the name that comes to the far left
　⇒　this SQL is 『Edit original data 2nd』
　⇒　taking out the name that will be displayed second from this Names table

LEFT JOIN condition
　⇒　N3.name < N4.name 
　⇒　N4.name is the second name to display, it will be after the N1.name
　⇒　The same name is not displayed, so comparison operator is 『<』 not 『<=』
　⇒　**Pick bold rows from the output of this SQL**

![re_9.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/08203bab-fd1b-e9a6-0fdd-1d697dd1a2fd.png)

# Pick the second name 2nd
```SQL:SQL
SELECT N3.name,MIN(N4.name)
FROM
(
	SELECT N1.name
	FROM Names AS N1 
	INNER JOIN Names AS N2 ON N1.name >= N2.name
	GROUP BY N1.name
	HAVING MOD(COUNT(*), 5) = 1
) AS N3(name) 
LEFT JOIN Names AS N4 ON N3.name < N4.name -- the second name
GROUP BY N3.name
```

▼ Output

By doing 『GROUP BY』 with the name(N3.name) that comes to the far left
the name to be displayed second is taken
　⇒　looking at the image above, it's the first row
　⇒　can be taken using MIN(N4.name) function


![re_10.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/f1f17a8f-7322-15e1-2d12-f48a1227fc2d.png)


# Pick the third name 1st
```SQL:SQL
SELECT N3.name,N4.name,N5.name
FROM
(
	SELECT N1.name
	FROM Names AS N1 
	INNER JOIN Names AS N2 ON N1.name >= N2.name
	GROUP BY N1.name
	HAVING MOD(COUNT(*), 5) = 1
) AS N3(name) 
LEFT JOIN Names AS N4 ON N3.name < N4.name -- the second name
LEFT JOIN Names AS N5 ON N4.name < N5.name -- the third name
```

Taking out the third name
LEFT JOIN two Names tables to the names that come to the far left
　⇒　taking out the second name from N4(we have done)
　⇒　taking out the third name from N5

Adding the second LEFT JOIN makes 79 rows of data with AI only
See how the second join creates combination data

the second join condition to retrieve the third name
　⇒　N4.name < N5.name
　⇒　the second name is Ben, the third name is after Charlie. 12 names are joined
　⇒　the second name is Chalie, the third name is after David. 11 names are joined
　⇒　the second name is David, the third name is after Ed. 10 names are joined
　　　・　　・　　・　　・　　・　　・
　　　・　　・　　・　　・　　・　　・
　⇒　the second name is Mike, the third name is after Neal. 1 name is joined
　⇒　the second name is Neal, no third name is joined

▼ Output(the image of the second LEFT JOIN)


![re_11.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/ae8578b7-e7ad-9966-f82d-ae3c5deef024.png)



----

▼ Output(whole combined data starting with Frank)

Similarly, AI, Ken's data exists

![re_12.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/9ea03e67-89d6-9d0a-1652-91fb49564a85.png)

# Pick the third name 2nd
```SQL:SQL
SELECT N3.name,MIN(N4.name),MIN(N5.name)
FROM
(
	SELECT N1.name
	FROM Names AS N1 
	INNER JOIN Names AS N2 ON N1.name >= N2.name
	GROUP BY N1.name
	HAVING MOD(COUNT(*), 5) = 1
) AS N3(name) 
LEFT JOIN Names AS N4 ON N3.name < N4.name -- the second name
LEFT JOIN Names AS N5 ON N4.name < N5.name -- the third name
GROUP BY N3.name
```

▼ Output(left side of image)

The way of taking out the third name is the same as the second name
　⇒　GROUP BY N3.name 
　⇒　We want to get one Greg from N4.name as the second name
　⇒　use MIN(N4.name)
　⇒　the third name is the first row of N5.name(Howard)
　⇒　use MIN(N5.name)


![re_13.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/791f07b0-e26c-1ee4-5c23-5aaa8d681e07.png)

------

First name is Frank's case
use three LEFT JOIN to retrieve the fourth name which is **Ida**

It will be 64 lines of data just by adding another LEFT JOIN
but only the first row data is needed, the others are not needed

We will need another LEFT JOIN to retrieve the fifth name
so more useless data will come out

First name is AI's case, there are more such data ・・・

If we try to retrieve 『names five across』 with this technique
**creating many rows that we don't use because of using four self LEFT JOIN**

But it is OK because we already know that these SQL are not practical
we should see them as techniques to get names

Another engineer posted different SQL to get the same result
without using self JOIN many times

I would like to share his idea in another article

▼Output(the original data to get 4th name)

![re_14.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/c105eebd-28d9-19a2-fda6-34e991c8280a.png)

# Answer SQL
```SQL:SQL
SELECT N3.name,MIN(N4.name),MIN(N5.name),MIN(N6.name),MIN(N7.name)
FROM
(
	SELECT N1.name
	FROM Names AS N1 
	INNER JOIN Names AS N2 ON N1.name >= N2.name
	GROUP BY N1.name
	HAVING MOD(COUNT(*), 5) = 1
) AS N3(name) 
LEFT JOIN Names AS N4 ON N3.name < N4.name -- the second name
LEFT JOIN Names AS N5 ON N4.name < N5.name -- the third name
LEFT JOIN Names AS N6 ON N5.name < N6.name -- the fourth name
LEFT JOIN Names AS N7 ON N6.name < N7.name -- the fifth name
GROUP BY N3.name
```

▼ Output

To retrive 4th and 5th name ・・・
Add LEFT JOIN as the same way to retrive the 2nd and 3rd names
and to pick the names use MIN(N6.name),MIN(N7.name)

briefly, this SQL is ・・・
pick names displayed on the first left side from the original data
created by the first INNER JOIN
then add 2nd, 3rd, 4th, 5th name using LEFT JOIN


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