Restructure Inline view

Question

SELECT MAX(column1)
FROM table1 B , table2 A, table3 H
WHERE B.unit=A.unit
AND B.value=A.value
AND B.unit=H.unit
AND B.value=H.value
AND A.number=1234

Can someone help me to restructure this query in inline view?

SAMPLE

Table1
------
Value  Unit 
001    A1
002    B1
003    C2
002    A1

Table2
--------
Value  Unit  Number
001    B4        11
002    B1      1234
004    B1        22

TABLE3
-------
VALUE  UNIT  NUMBER  COLUMN1
001    B4        11      555
002    B1      1234      557
002    B1      1234      559

OUTPUT
------
MAX(C0LUMN1)
-----------
559

Answer 1

In your query there is no need for inlineview :- if that is rewritten in inlineview it will be like

Select Max(Column1)
 From  (Select Value,Unit From Table1)B,
(Select Value,Unit,Number From Table2)A,
 Table3 as H
 Where B.Unit=A.Unit
 And B.Value=A.Value
AND B.unit=H.unit
And B.Value=H.Value
AND A.number=1234;

Below is the example when to use inline view hope this help!!!

An inline view is a SELECT statement in the FROM clause. As mentioned in the View section, a view is a virtual table that has the characteristics of a table yet does not hold any actual data. In an inline view construct, instead of specifying table name(s) after the FROM keyword, the source of the data actually comes from a view that is created within the SQL statement. The syntax for an inline view is,

SELECT "column_name" FROM (Inline View);

When should we use inline view? Below is an example:

Assume we have two tables: The first table is User_Address, which maps each user to a ZIP code; the second table is User_Score, which records all the scores of each user. The question is, how to write a SQL query to find the number of users who scored higher than 200 for each ZIP code?

Without using an inline view, we can accomplish this in two steps:

Query 1

CREATE TABLE User_Higher_Than_200
SELECT User_ID, SUM(Score) FROM User_Score
GROUP BY User_ID
HAVING SUM(Score) > 200;

Query 2

SELECT a2.ZIP_CODE, COUNT(a1.User_ID)
FROM User_Higher_Than_200 a1, User_Address a2
WHERE a1.User_ID = a2.ZIP_CODE
GROUP BY a2.ZIP_CODE;

In the above code, we introduced a temporary table, User_Higher_Than_200, to store the list of users who scored higher than 200. User_Higher_Than_200 is then used to join to the User_Address table to get the final result.

We can simplify the above SQL using the inline view construct as follows:

Query 3

     SELECT a2.ZIP_CODE, COUNT(a1.User_ID)
        FROM
        (SELECT User_ID, SUM(Score) FROM 
        User_Score GROUP BY User_ID HAVING SUM(Score) > 200) a1,
        User_Address a2
        WHERE a1.User_ID = a2.ZIP_CODE
        GROUP BY a2.ZIP_CODE;

There are two advantages on using inline view here:

1. We do not need to create the temporary table. This prevents the database from having too many objects, which is a good thing as each additional object in the database costs resources to manage.

2. We can use a single SQL query to accomplish what we want Notice that we treat the inline view exactly the same as we treat a table. Comparing Query 2 and Query 3, we see that the only difference is we replace the temporary table name in Query 2 with the inline view statement in Query 3. Everything else stays the same.

Inline view is sometimes referred to as derived table. These two terms are used interchangeably.

Answer 2

I need to show column from other table that have the max column value

SELECT MAX( H.column1 ) AS max_column1,
       MAX( A.number ) KEEP ( DENSE_RANK LAST ORDER BY H.column1 ) AS max_number
FROM   table1 B
       INNER JOIN table2 A
       ON ( B.unit = A.unit AND B.value = A.value )
       INNER JOIN table3 H
       ON ( B.unit = H.unit AND B.value = H.value )
WHERE  A.number=1234