Select in the Select: Magic?

I always found it a supernatural phenomenon the magic used in SQL queries when involved within Selects Selects. I found it very funny when I got the email from Thiago, company intern here, a query about what I had done and sent him:

This method is its magical means, sounds like the kind of thing that “should not work but it works” when u look! Not cuz it makes no sense (pq is), pq but these databases to imply things much simpler than that… Ultimately, works, qdo same is not to seek for a specific code­am (as you did before), which is great. (Thiago)

The funny thing about this is that I also never stopped to think through how it worked, simply used. Yes, wore it like magic, but now decided to study the background for how this solves my problems and decided to share here with you.

Attention: If you do not know anything about computer, never heard of SQL, query and other terms in this article, can stop reading here because the business is too technical and boring! Rsrsr Eu, eg, could not read the second paragraph of text that talk about the last wall of the report.

To start, I will soon show the solution. Below is a SQL query that, for us today, consists of pure magic:

   1:  SELECT

   2:    a1.*

   3:  FROM

   4:    Acao a1

   5:  WHERE

   6:    a1.situacaoAcao <> 'E' AND

   7:    a1.dtAlteracaoAcao = (

   8:      SELECT

   9:        MAX(a2.dtAlteracaoAcao)

  10:      FROM

  11:        Acao a2

  12:      WHERE

  13:        a2.indAcaoOriginal = a1.indAcaoOriginal

  14:      )

What it does? Returns all the Shares not delete­and only the most current. Single born? Yes, it is exactly what I wanted.

To better understand, let's see the table structure Action:

*The point in this table observer, is the relationship between the self-field indAcaoOriginal to indAcao. An action is never updated. If you need some updating, a new record is created, with new information, but with the indAcaoOriginal pointing to the first action that was created. So, we have a history of updates Shares.

Knowing what you have in the bank, a lot of individual actions (indAcao) auto-related (indAcaoOriginal) with the original Action, the challenge was to bring all the latest Actions, in other words, with the last change date.

Debunking the Magic

Let's use the table below as an example of records. It is the return of the following query:

   1:  SELECT

   2:    a1.*

   3:  FROM

   4:    Acao a1

Filtering records by deleting­two, we have the following result:

   1:  SELECT

   2:    a1.*

   3:  FROM

   4:    Acao a1

   5:  WHERE

   6:    a1.situacaoAcao <> 'E'

*Only action indAcao 11 was removed.

If you run the query below in isolation, we have the following result:

   1:  SELECT

   2:    MAX(a2.dtAlteracaoAcao)

   3:  FROM

   4:    Acao a2

*The MAX function() returns the largest value of a field in a set, in this case isolated, set corresponds to all records of the table action. It returns the record 19 because it has the largest data.

Then, I need to do to rescue the largest data set of the Original Shares? I need to generate a separate set, containing all Original Shares, to rescue this largest data. This is obvious. It's good that we know what we want to define. The problem is to talk to SQL him understand that. :)

Let's run a Chinese, step-by-step, query to look at how the magic really works.

Step-by-Step

The first thing that makes the query below, rescue is different from the first record delete­do da tabela a1.

   1:  SELECT

   2:    a1.*

   3:  FROM

   4:    Acao a1

   5:  WHERE

   6:    a1.situacaoAcao <> 'E' AND

   7:    a1.dtAlteracaoAcao = (

   8:      SELECT

   9:        MAX(a2.dtAlteracaoAcao)

  10:      FROM

  11:        Acao a2

  12:      WHERE

  13:        a2.indAcaoOriginal = a1.indAcaoOriginal

  14:      )

Having this record, the internal query below, creates the following subset:

   1:  SELECT

   2:    MAX(a2.dtAlteracaoAcao)

   3:  FROM

   4:    Acao a2

   5:  WHERE

   6:    a2.indAcaoOriginal = a1.indAcaoOriginal

She rescues all records from the table action (a2) that have a2.indAcaoOriginal a1.indAcaoOriginal equal to the current record of the table action (a1). What is the value of the first record in a1.indAcaoOriginal rescued by Select external? The value is 1, then, returns all records that have the indAcaoOriginal a2 equals 1. Simple as that.

Having this subset (indAcaoOriginal = 1), select the internal returns, by MAX, most registered date, in this case, it is 17/03/2006 18:03.

Now, knowing that more time is 17/03/2006 18:03, select the external (a1), compares with the date of its registration.

As we can verify, MAX on the date returned is not equal to date of our first record, in other words, the select will ignore this line, not displaying the, and go to the second record:

Soon, here he will do the same steps explained above and verify that the date remains different from the date of the largest subset of the Original Shares.

Thus, select the entire table travel, returning only those records that possess­rem the largest data.

Easy né? I'm feeling the Mr. M, to reveal the magic involved in this query. :)

And there­ Thiago, understood?