SQL Join Tables with Group By and Order By

In this post of easy lesson series, we are going to discuss the usage of GROUP BY and ORDER BY clause within a JOIN clause. First of all you need to know what is a sql join clause.

A SQL join clause combines records from two or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining fields from two tables (or more) by using values common to each.

Sample table : agents

+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | john doe             | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Lawrence             | London             |       0.13 | 075-12458969    |         |
| A008       | Christlin            | New York           |       0.12 | 044-25874365    |         |
| A011       | Kowsik Prasanth      | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Jones Rajakumar      | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Dinesh Kumar         | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

Sample table : orders

   ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE  CUST_CODE       AGENT_CODE      ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
    200114       3500           2000 15-AUG-08 C00002          A008
    200122       2500            400 16-SEP-08 C00003          A004
    200118        500            100 20-JUL-08 C00023          A006
    200119       4000            700 16-SEP-08 C00007          A010
    200121       1500            600 23-SEP-08 C00008          A004
    200130       2500            400 30-JUL-08 C00025          A011
    200134       4200           1800 25-SEP-08 C00004          A005
    200115       2000           1200 08-FEB-08 C00013          A013
    200108       4000            600 15-FEB-08 C00008          A004
    200103       1500            700 15-MAY-08 C00021          A005
    200105       2500            500 18-JUL-08 C00025          A011
    200109       3500            800 30-JUL-08 C00011          A010
    200101       3000           1000 15-JUL-08 C00001          A008
    200111       1000            300 10-JUL-08 C00020          A008
    200104       1500            500 13-MAR-08 C00006          A004
    200106       2500            700 20-APR-08 C00005          A002
    200125       2000            600 10-OCT-08 C00018          A005
    200117        800            200 20-OCT-08 C00014          A001
    200123        500            100 16-SEP-08 C00022          A002
    200120        500            100 20-JUL-08 C00009          A002
    200116        500            100 13-JUL-08 C00010          A009
    200124        500            100 20-JUN-08 C00017          A007
    200126        500            100 24-JUN-08 C00022          A002
    200129       2500            500 20-JUL-08 C00024          A006
    200127       2500            400 20-JUL-08 C00015          A003
    200128       3500           1500 20-JUL-08 C00009          A002
    200135       2000            800 16-SEP-08 C00007          A010
    200131        900            150 26-AUG-08 C00012          A012
    200133       1200            400 29-JUN-08 C00009          A002
    200132       4000           2000 15-AUG-08 C00013          A013
    200100       1000            600 08-JAN-08 C00015          A003
    200110       3000            500 15-APR-08 C00019          A010
    200107       4500            900 30-AUG-08 C00007          A010
    200112       2000            400 30-MAY-08 C00016          A007
    200113       4000            600 10-JUN-08 C00022          A002
    200102       2000            300 25-MAY-08 C00012          A012
To get ‘agent_code’ and ‘agent_name’ columns from the table ‘agents’ and sum of ‘advance_amount’ column from the table ‘orders’ after a joining, with following conditions –

1. ‘agent_code’ of ‘agents’ and ‘orders’ must be same,

2. the same combination of ‘agent_code’ and ‘agent_name’ of ‘agents’ table must be within a group,

3. ‘agent_code’ of ‘agents’ table should arrange in a order, default is ascending order,

the following sql statement can be used :

SELECT agents.agent_code,agents.agent_name, SUM(orders.advance_amount) FROM agents,orders  WHERE agents.agent_code=orders.agent_code GROUP BY agents.agent_code,agents.agent_name ORDER BY agents.agent_code;

the following sql statement is equivalent version of the above statement (see the JOIN keyword):

SELECT agents.agent_code,agents.agent_name, SUM(orders.advance_amount) FROM agents INNER JOIN orders ON orders.agent_code = agents.agent_code GROUP BY agents.agent_code,agents.agent_name ORDER BY agents.agent_code;

Above queries may look different but the working principles and output are the same. So both the queries will give the following output when you run them independently.

NOTE: The column name utilized by the ORDER BY clause should be contained in either an aggregate function or the GROUP BY clause.

Output

AGENT_CODE AGENT_NAME                               SUM(ORDERS.ADVANCE_AMOUNT)
---------- ---------------------------------------- --------------------------
A001       Dinesh Kumar                                                    200
A002       Mukesh                                                         3500
A003       Lawrence                                                       1000
A004       Ivan                                                           2100
A005       Anderson                                                       3100
A006       McDen                                                           600
A007       John Doe                                                        500
A008       Christlin                                                      3300
A009       Benjamin                                                        100
A010       Jones Rajakumar                                                3700
A011       Kowsik Prasanth                                                 900
A012       Lucida                                                          450

Easy Lesson On : Active FTP vs Passive FTP

Contents:

 

Introduction

One of the most commonly seen questions when dealing with firewalls and other Internet connectivity issues is the difference between active and passive FTP and how best to support either or both of them. Hopefully the following text will help to clear up some of the confusion over how to support FTP in a firewalled environment.

 

The Basics

FTP is a TCP based service exclusively. There is no UDP component to FTP. FTP is an unusual service in that it utilizes two ports, a ‘data’ port and a ‘command’ port (also known as the control port). Traditionally these are port 21 for the command port and port 20 for the data port. The confusion begins however, when we find that depending on the mode, the data port is not always on port 20.

 

Active FTP

In active mode FTP the client connects from a random unprivileged port (N > 1023) to the FTP server’s command port, port 21. Then, the client starts listening to port N+1 and sends the FTP command PORT N+1 to the FTP server. The server will then connect back to the client’s specified data port from its local data port, which is port 20.

From the server-side firewall’s standpoint, to support active mode FTP the following communication channels need to be opened:

  • FTP server’s port 21 from anywhere (Client initiates connection)
  • FTP server’s port 21 to ports > 1023 (Server responds to client’s control port)
  • FTP server’s port 20 to ports > 1023 (Server initiates data connection to client’s data port)
  • FTP server’s port 20 from ports > 1023 (Client sends ACKs to server’s data port)

When drawn out, the connection appears as follows:active-ftp-winged-post

In step 1, the client’s command port contacts the server’s command port and sends the command PORT 1027. The server then sends an ACK back to the client’s command port in step 2. In step 3 the server initiates a connection on its local data port to the data port the client specified earlier. Finally, the client sends an ACK back as shown in step 4.

The main problem with active mode FTP actually falls on the client side. The FTP client doesn’t make the actual connection to the data port of the server–it simply tells the server what port it is listening on and the server connects back to the specified port on the client. From the client side firewall this appears to be an outside system initiating a connection to an internal client–something that is usually blocked.

 

Active FTP Example

Below is an actual example of an active FTP session. The only things that have been changed are the server names, IP addresses, and user names. In this example an FTP session is initiated from area1.wingedpost.com (192.168.150.80), a linux box running the standard FTP command line client, to area2.wingedpost.com (192.168.150.90), a linux box running ProFTPd 1.2.2RC2. The debugging (-d) flag is used with the FTP client to show what is going on behind the scenes. Everything in red is the debugging output which shows the actual FTP commands being sent to the server and the responses generated from those commands. Normal server output is shown in black, and user input is in bold.

There are a few interesting things to consider about this dialog. Notice that when the PORT command is issued, it specifies a port on the client (192.168.150.80) system, rather than the server. We will see the opposite behavior when we use passive FTP. While we are on the subject, a quick note about the format of the PORT command. As you can see in the example below it is formatted as a series of six numbers separated by commas. The first four octets are the IP address while the last two octets comprise the port that will be used for the data connection. To find the actual port multiply the fifth octet by 256 and then add the sixth octet to the total. Thus in the example below the port number is ( (14*256) + 178), or 3762. A quick check with netstat should confirm this information.

area1: {/home/p-t/wingedpost/public_html} % ftp -d area2
Connected to area2.wingedpost.com.
220 area2.wingedpost.com FTP server ready.
Name (area2:wingedpost): wingedpostuser
---> USER wingedpostuser
331 Password required for wingedpostuser.
Password: TmpPass
---> PASS XXXX
230 User wingedpostuser logged in.
---> SYST
215 UNIX Type: L8
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> ls
ftp: setsockopt (ignored): Permission denied
---> PORT 192,168,150,80,14,178
200 PORT command successful.
---> LIST
150 Opening ASCII mode data connection for file list.
drwx------   3 wingedpostuser    users         104 Jul 27 01:45 public_html
226 Transfer complete.
ftp> quit
---> QUIT
221 Goodbye.

 

Passive FTP

In order to resolve the issue of the server initiating the connection to the client a different method for FTP connections was developed. This was known as passive mode, or PASV, after the command used by the client to tell the server it is in passive mode.

In passive mode FTP the client initiates both connections to the server, solving the problem of firewalls filtering the incoming data port connection to the client from the server. When opening an FTP connection, the client opens two random unprivileged ports locally (N > 1023 and N+1). The first port contacts the server on port 21, but instead of then issuing a PORT command and allowing the server to connect back to its data port, the client will issue the PASV command. The result of this is that the server then opens a random unprivileged port (P > 1023) and sends P back to the client in response to the PASV command. The client then initiates the connection from port N+1 to port P on the server to transfer data.

From the server-side firewall’s standpoint, to support passive mode FTP the following communication channels need to be opened:

  • FTP server’s port 21 from anywhere (Client initiates connection)
  • FTP server’s port 21 to ports > 1023 (Server responds to client’s control port)
  • FTP server’s ports > 1023 from anywhere (Client initiates data connection to random port specified by server)
  • FTP server’s ports > 1023 to remote ports > 1023 (Server sends ACKs (and data) to client’s data port)

When drawn, a passive mode FTP connection looks like this:passive-ftp-wingedpost

In step 1, the client contacts the server on the command port and issues the PASV command. The server then replies in step 2 with PORT 2024, telling the client which port it is listening to for the data connection. In step 3 the client then initiates the data connection from its data port to the specified server data port. Finally, the server sends back an ACK in step 4 to the client’s data port.

While passive mode FTP solves many of the problems from the client side, it opens up a whole range of problems on the server side. The biggest issue is the need to allow any remote connection to high numbered ports on the server. Fortunately, many FTP daemons, including the popular WU-FTPD allow the administrator to specify a range of ports which the FTP server will use.

The second issue involves supporting and troubleshooting clients which do (or do not) support passive mode. As an example, the command line FTP utility provided with Solaris does not support passive mode, necessitating a third-party FTP client, such as ncftp. 
NOTE: This is no longer the case–use the -p option with the Solaris FTP client to enable passive mode!

With the massive popularity of the World Wide Web, many people prefer to use their web browser as an FTP client. Most browsers only support passive mode when accessing ftp:// URLs. This can either be good or bad depending on what the servers and firewalls are configured to support.

 

Passive FTP Example

Below is an actual example of a passive FTP session. The only things that have been changed are the server names, IP addresses, and user names. In this example an FTP session is initiated from area1.wingedpost.com (192.168.150.80), a linux box running the standard FTP command line client, to area2.wingedpost.com (192.168.150.90), a linux box running ProFTPd 1.2.2RC2. The debugging (-d)  flag is used with the FTP client to show what is going on behind the scenes. Everything in red is the debugging output which shows the actual FTP commands being sent to the server and the responses generated from those commands. Normal server output is shown in black, and user input is in bold.

Notice the difference in the PORT command in this example as opposed to the active FTP example. Here, we see a port being opened on the server (192.168.150.90) system, rather than the client.

area1: {/home/p-t/wingedpost/public_html} % ftp -d area2
Connected to area2.wingedpost.com.
220 area2.wingedpost.com FTP server ready.
Name (area2:wingedpost): wingedpostuser
---> USER wingedpostuser
331 Password required for wingedpostuser.
Password: TmpPass
---> PASS XXXX
230 User wingedpostuser logged in.
---> SYST
215 UNIX Type: L8
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> passive
Passive mode on.
ftp> ls
ftp: setsockopt (ignored): Permission denied
---> PASV
227 Entering Passive Mode (192,168,150,90,195,149).
---> LIST
150 Opening ASCII mode data connection for file list
drwx------   3 wingedpostuser    users         104 Jul 27 01:45 public_html
226 Transfer complete.
ftp> quit
---> QUIT
221 Goodbye.

 

Summary

The following chart should help admins remember how each FTP mode works:

 Active FTP :
     command : client >1023 -> server 21
     data    : client >1023 <- server 20

 Passive FTP :
     command : client >1023 -> server 21
     data    : client >1024 -> server >1023

A quick summary of the pros and cons of active vs. passive FTP is also in order:

Active FTP is beneficial to the FTP server admin, but detrimental to the client side admin. The FTP server attempts to make connections to random high ports on the client, which would almost certainly be blocked by a firewall on the client side. Passive FTP is beneficial to the client, but detrimental to the FTP server admin. The client will make both connections to the server, but one of them will be to a random high port, which would almost certainly be blocked by a firewall on the server side.

Luckily, there is somewhat of a compromise. Since admins running FTP servers will need to make their servers accessible to the greatest number of clients, they will almost certainly need to support passive FTP. The exposure of high level ports on the server can be minimized by specifying a limited port range for the FTP server to use. Thus, everything except for this range of ports can be firewalled on the server side. While this doesn’t eliminate all risk to the server, it decreases it tremendously.

References

An excellent reference on how various internet protocols work and the issues involved in firewalling them can be found in the O’Reilly and Associates book, Building Internet Firewalls, 2nd Ed, by Brent Chapman and Elizabeth Zwicky. 
Note : This book is VERY old and the information contained therein may be outdated!

Finally, the definitive reference on FTP would be RFC 959, which sets forth the official specifications of the FTP protocol. RFCs can be downloaded from numerous locations, including http://www.faqs.org/rfcs/rfc959.html.