Visualize SQL | Report Formatting(part 3)

# 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 『part3』, the idea of finding the row we want to retrieve
with the MOD function is the same as 『part1』or『part2』
but the way to retrieve the name is completely different

This SQL use JOIN only once when creating the original data
and it does not use JOIN to pick and display names.
It looks simple, but it's a cleverly thought out SQL

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/2418d97d-da35-3231-78c7-1754b13ae205.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/3ab341ee-f47f-c621-6aad-34d195e2b7aa.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/49aff3a8-e6bb-0df6-a70e-a3e3cab8311d.png)

▼ Output(It is rearranged)

![re_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/4bfc640a-f749-611a-ceb1-15f56c060759.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/19b2e602-786c-5d74-9c63-f244196405fc.png)


Up to this point, it is the same as 『part2』 
but the approach changes from here


# Edit original data(1st name)①
```SQL:SQL
SELECT 
  N1.name, 
 (SELECT COUNT(*) FROM Names AS N3 WHERE N1.name <= N3.name),
 (SELECT array_agg(N3.name) FROM Names AS N3 WHERE N1.name <= N3.name)

FROM Names AS N1
INNER JOIN Names AS N2 ON N1.name >= N2.name
GROUP BY N1.name
```

Creating 14 rows of unique name data by 『GROUP BY N1.name』

`(SELECT COUNT(*) FROM Names AS N3 WHERE N1.name <= N3.name)`
　⇒　pick names that fit condition of 『N1.name <= N3.name』 from Names tabl
　　　then COUNT(＊)
　⇒　from the top, 14 -> 13 -> 12 -> ・・・ 3 -> 2 -> 1 
　⇒　It's a continuous number
　⇒　Use this number to retrieve the name of a particular row

`(SELECT array_agg(N3.name) FROM Names AS N3 ・・・)`
　⇒　Checking the contents of COUNT(＊)
　⇒　this is counting a set of N3.name


▼ Output(image top)

![re_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/00395764-591b-16eb-88d9-6ca753fad2e0.png)

# Edit original data(1st name)②
```SQL:SQL
SELECT 
  N1.name, 
 (SELECT COUNT(*) FROM Names AS N3 WHERE N1.name <= N3.name),
 (SELECT MOD(COUNT(*), 5) FROM Names AS N3 WHERE N1.name <= N3.name), -- add
 (SELECT array_agg(N3.name) FROM Names AS N3 WHERE N1.name <= N3.name)

FROM Names AS N1
INNER JOIN Names AS N2 ON N1.name >= N2.name
GROUP BY N1.name
```

Add `MOD(COUNT(＊), 5)` in SELECT statement
　⇒　remainder of (counting a set of names ÷ 5)
　⇒　4 -> 3 -> 2 -> 1 -> 0 -> 4 -> 3 ・・・・
　⇒　`It will be a number from 0 to 4`
　⇒　In other words, **there are five groups**
　⇒　we use this number to display 『names five across』


▼ Output

![re_7.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/b3b4eabb-037d-8220-399b-b8f0aeb443f7.png)


# Edit original data(1st name)③
```SQL:SQL
SELECT 
  N1.name, 
 (SELECT COUNT(*) FROM Names),
 (SELECT MOD(COUNT(*), 5) FROM Names)

FROM Names AS N1
INNER JOIN Names AS N2 ON N1.name >= N2.name
GROUP BY N1.name
```

▼ Output

`SELECT COUNT(*) FROM Names`
　⇒　There are 14 names in Names table and count them

`SELECT MOD(COUNT(*), 5) FROM Names`
　⇒　remainder of (count of Names table ÷ 5)
　⇒　remainder of (14 ÷ 5) is 4
　⇒　we use this number to display 『names five across』


![re_8.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/d049556a-34c7-f1b3-636e-ff5ea49e99b0.png)



# Edit original data(1st name)④
```SQL:SQL
SELECT 
  N1.name,
  (SELECT MOD(COUNT(*), 5) FROM Names AS N3 WHERE N1.name <= N3.name),
  (SELECT MOD(COUNT(*), 5) FROM Names),

  (SELECT MAX(N1.name) FROM Names AS N3 WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD(COUNT(*), 5) FROM Names)
  ),
  (SELECT array_agg(N1.name) FROM Names AS N3 WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD(COUNT(*), 5) FROM Names)
  )
FROM Names AS N1
INNER JOIN Names AS N2 ON N1.name >= N2.name
GROUP BY N1.name
```

Taking out the name of the first person.
I will explain in detail how to take it out

`SELECT MOD(COUNT(*), 5) FROM Names AS N3 WHERE N1.name <= N3.name`
　⇒　**The output of this MOD function is a numerical value**
　　　**for classifying into 5 groups from 0 to 4 (red line)**
　⇒　Number to identify a column of 『names five across』
　⇒　From now on we will call it **class number**
　⇒　Use this MOD function in HAVING clause
　⇒　use this class number to take out 2nd, 3rd ・・・names as well

`SELECT MOD(COUNT(*), 5) FROM Names`
　⇒　**The output of this MOD function is a number to determine**
　　　**the row that we want to retrieve a name(blue line)**
　⇒　Use this MOD function in HAVING clause
　⇒　**It is a constant for taking out class number(4)**

Write two MOD functions in HAVING clause as a condition
　⇒　The same return values ​of the two mod functions is the row we want
　⇒　row where 4 = 4
　⇒　AI, Frank, Ken match this condition

We want to retrieve the name(N1.name), but can't do it as it is
　⇒　Looking at array_agg (N1.name), it's a set of names
　⇒　Only one name is taken using MAX(N1.name)
　⇒　MIN(N1.name) is also fine as long as it can be aggregated


![re_9.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/e6039e3d-2e90-feae-e7f2-7974a99ec050.png)



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

▼ Output(left side of image)

The first columns(N1.name) and 1st name of 『names five across』 are left.
Right side of image is how to retrive 2nd,3rd,4th and 5th names

We try to retrive 2nd name using the same way next


![re_10.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/febbe565-15b1-3a31-87da-417d3233a301.png)

# Edit original data(2nd name)①
```SQL:SQL
SELECT 
	N1.name,
	(SELECT COUNT(*) FROM Names),
	(SELECT MOD(COUNT(*)-1, 5) FROM Names)

FROM Names AS N1
INNER JOIN Names AS N2 ON N1.name >= N2.name
GROUP BY N1.name
```

▼ Output

This is almost the same as 『 Edit original data(1st name)③ 』SQL
The difference is this part　⇒　`COUNT(*) -1`

I mentioned before ・・・
`SELECT MOD(COUNT(*)-1, 5) FROM Names` is  a constant
that specifies rows where we want to retrieve a name


The original data(N1.name) is in alphabetical order with 14 rows
　⇒　When we picked 1st, the name(N1.name) was classified into numbers from 0 to 4
　　　using below MOD function
　⇒　`SELECT MOD(COUNT(*), 5) FROM Names AS N3 WHERE N1.name <= N3.name`
　⇒　we call this number as `class number`
　⇒　Specify the row we want to retrieve with `SELECT MOD(COUNT(*)-1, 5) FROM Names`
　⇒　we are going to pick the row with **class number 3** from N1.name


![re_11.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/f061fbee-759e-9cb4-f0f6-1b734994a85c.png)

# Edit original data(2nd name)②
```SQL:SQL
SELECT 
  N1.name,
  (SELECT MOD(COUNT(*), 5) FROM Names AS N3 WHERE N1.name <= N3.name),
  (SELECT MOD(COUNT(*)-1, 5) FROM Names),

  (SELECT MAX(N1.name) FROM Names AS N3 WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD(COUNT(*)-1, 5) FROM Names)
  ),
  (SELECT array_agg(N1.name) FROM Names AS N3 WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD(COUNT(*)-1, 5) FROM Names)
  )
FROM Names AS N1
INNER JOIN Names AS N2 ON N1.name >= N2.name
GROUP BY N1.name
```


It is the same idea as taking out the first name

[1] the second column in SELECT statement
　⇒　(SELECT MOD(COUNT(*), 5) FROM Names AS N3 WHERE N1.name <= N3.name)
　⇒　N1.name is lined up vertically with 14 names
　⇒　these names are classified by numbers from 0 to 4 (**class number**)

[2] the third column in SELECT statement
　⇒　SELECT MOD(COUNT(＊)-1, 5) FROM Names
　⇒　the 2nd name we want to retrieve is the row which **class number is 3**


Write [1] and [2] in HAVING clause as a condition
　⇒　row where 3 = 3
　⇒　Ben, Greg, Larry match this condition

N1.name is a set of names so use MAX() function to display one name
　⇒　Max(N1.name)

![re_12.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/426ef192-1116-c859-9b69-e1a27d2588c5.png)

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

▼ Output

The first columns(N1.name) and 2nd name of 『names five across』 are left.

We retrieve the 1st and 2nd names at the same time next.

![re_13.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/0fd31f6c-4088-3c63-33e6-91d07eedc43b.png)

# Taking out 1st and 2nd names
```SQL:SQL
SELECT 
  N1.name,
  (SELECT MAX(N1.name) FROM Names AS N3 WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD(COUNT(*), 5) FROM Names)
  ),
  (SELECT MAX(N1.name) FROM Names AS N3 WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD(COUNT(*)-1, 5) FROM Names)
  )
FROM Names AS N1
INNER JOIN Names AS N2 ON N1.name >= N2.name
GROUP BY N1.name
```

▼ Output

Line up two SELECT statements that retrive 1st and 2nd names
Looking at these, we can imagine to retrive 3rd, 4th and 5th names

we can retrive other names by adjusting COUNT(＊) in
 `SELECT MOD(COUNT(*), 5) FROM Names` that specify the row we want to pick

![re_14.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/f40a3f17-90cf-36de-1533-a5cf2963dccc.png)

# Taking out 1st ～ 5th names
```SQL:SQL
SELECT
  N1.name,
  (COUNT(*) - 1) / 5,

  (SELECT MOD(COUNT(*), 5) FROM Names AS N3 WHERE N1.name <= N3.name),

  (SELECT MAX(N1.name) FROM Names AS N3 WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD(COUNT(*), 5) FROM Names)
  ),
  (SELECT MAX(N1.name) FROM Names AS N3 WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD(COUNT(*)-1, 5) FROM Names)
  ),
  (SELECT MAX(N1.name) FROM Names AS N3 WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD(COUNT(*)-2, 5) FROM Names)
  ),
  (SELECT MAX(N1.name) FROM Names AS N3 WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD(COUNT(*)-3, 5) FROM Names)
  ),
  (SELECT MAX(N1.name) FROM Names AS N3 WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD(COUNT(*)-4, 5) FROM Names)
  )
FROM Names AS N1
INNER JOIN Names AS N2 ON N1.name >= N2.name
GROUP BY N1.name
```

The third column in SELECT statement
　⇒　(SELECT MOD(COUNT(＊), 5) FROM Names AS N3 WHERE N1.name <= N3.name)
　⇒　use this MOD() function in HAVING clause
　⇒　this is class number

Adjust `(SELECT MOD(COUNT(*), 5) FROM Names)` that specify the name we want
　⇒　decide the row(class number)

～～～～～～～～～～～～～～～～～～～～～～～～～～
Taking out the 1st name(class number is 4)
　⇒　(SELECT MOD(COUNT(＊), 5) FROM Names)
　⇒　remainder of (14 ÷ 5) is 4
～～～～～～～～～～～～～～～～～～～～～～～～～～
Taking out the 2nd name(class number is 3)
　⇒　(SELECT MOD(COUNT(＊)**-1**, 5) FROM Names)
　⇒　remainder of (13 ÷ 5) is 3
～～～～～～～～～～～～～～～～～～～～～～～～～～
Taking out the 3rd name(class number is 2)
　⇒　(SELECT MOD(COUNT(＊)**-2**, 5) FROM Names)
　⇒　remainder of (12 ÷ 5) is 2
～～～～～～～～～～～～～～～～～～～～～～～～～～
Taking out the 4th name(class number is 1)
　⇒　(SELECT MOD(COUNT(＊)**-3**, 5) FROM Names)
　⇒　remainder of (11 ÷ 5) is 1
～～～～～～～～～～～～～～～～～～～～～～～～～～
Taking out the 5th name(class number is 0)
　⇒　(SELECT MOD(COUNT(＊)**-4**, 5) FROM Names)
　⇒　remainder of (10 ÷ 5) is 0
～～～～～～～～～～～～～～～～～～～～～～～～～～

▼ Output

![re_15.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/72a1714b-f40d-4c0b-8477-36ea087f3351.png)


It looks like 5 names are lined up side by side
but they are not in the same row

we are very close to the end ・・・

we need to remove 『blank space』 from the output
before doing that let's me explain about `(COUNT(*) - 1) / 5` 
　⇒　N1.name is aggregated (GROUP BY N1.name)
　⇒　COUNT(＊) is the row number that N1.name has
　⇒　It is the same as the number of N2.name that are joined to N1.name
　⇒　take a look the image of the original data below
　⇒　**Use this quotient to classify into 3 groups of 0, 1, 2**

■ `(COUNT(*) - 1) / 5` calculation
quotient of　(1-1)  ÷ 5 is 0 ・・・ AI
quotient of　(2-1)  ÷ 5 is 0 ・・・ Ben
quotient of　(3-1)  ÷ 5 is 0 ・・・ Charlie
quotient of　(4-1)  ÷ 5 is 0 ・・・ David
quotient of　(5-1)  ÷ 5 is 0 ・・・ Ed
quotient of　(6-1)  ÷ 5 is 1 ・・・ Frank
quotient of　(7-1)  ÷ 5 is 1 ・・・ Greg
quotient of　(8-1)  ÷ 5 is 1 ・・・ Howard
quotient of　(9-1)  ÷ 5 is 1 ・・・ Ida
quotient of　(10-1) ÷ 5 is 1 ・・・ Joe
quotient of　(11-1) ÷ 5 is 2 ・・・ Ken
quotient of　(12-1) ÷ 5 is 2 ・・・ Larry
quotient of　(13-1) ÷ 5 is 2 ・・・ Mike
quotient of　(14-1) ÷ 5 is 2 ・・・ Neal

![re_16.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/2d68c633-93ab-8ddd-9ee9-91165efc979f.png)

# Answer SQL
```SQL:SQL
SELECT MAX(name1), MAX(name2), MAX(name3), MAX(name4), MAX(name5)
FROM
(
  SELECT
    (COUNT(*) - 1) / 5, 

    (SELECT MAX(N1.name) FROM Names AS N3
     WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5)= (SELECT MOD(COUNT(*), 5) FROM Names)),
			
    (SELECT MAX(N1.name) FROM Names AS N3
     WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD((COUNT(*) - 1), 5) FROM Names)),
			
    (SELECT MAX(N1.name) FROM Names AS N3
     WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD((COUNT(*) - 2), 5) FROM Names)),

    (SELECT MAX(N1.name) FROM Names AS N3
     WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD((COUNT(*) - 3), 5) FROM Names)),

    (SELECT MAX(N1.name) FROM Names AS N3
     WHERE N1.name <= N3.name
     HAVING MOD(COUNT(*), 5) = (SELECT MOD((COUNT(*) - 4), 5) FROM Names))
  FROM Names AS N1
  INNER JOIN Names AS N2 ON N1.name >= N2.name
  GROUP BY N1.name
) AS X0(cnt, name1, name2, name3, name4, name5)
GROUP BY cnt;
```

The point is  `(COUNT(*) - 1) / 5` that we added before
　⇒　There are three numbers, 0, 1, 2(cnt)
　⇒　14 rows of data are divided into three from the top by 5,5,4
　⇒　Aggregate(GROUP BY) the whole with this number(cnt)
　⇒　3 rows are left (0,1,2)
　⇒　Each N1.name is not aggregate variable, so it cannot be displayed as it is
　⇒　Aggregate N1.name using MAX() function
　⇒　N1.name is renamed as name1,name2,name3,name4 and name5
　⇒　taking out one name from N1.name with blank space

▼ Output(top of the image)

![re_17.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/9df1bb4b-b8ae-03fd-fb54-66532dd5e2e3.png)


> personally ・・・
>
> when I extract data with SQL
>『how to join data?』comes out in my head
>
> However, this SQL displays 『five name across』
> using classification(grouping) not using JOIN
>
> It is wonderful・・・
>
>

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