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

The question is quite unclear. What's wrong with this query? What exactly are you trying to achieve? — Mureinik, Jun 13 '16 at 10:11
can this be made with inline view. like max fields to be re-structure with condition rownum =1 — user1954762, Jun 13 '16 at 10:13
It still isn't clear. You have no non-aggregate fields or group-by clause so you'll only have one row in the result set anyway. Edit the question to show an example with initial data, what you get now, and what you want to see (and why). — Alex Poole, Jun 13 '16 at 10:23
That still doesn't tell us what you want - you have a single row in the output. What will an inline view or rownum filter achieve? — Alex Poole, Jun 13 '16 at 10:50
it's an sample input with same data...consider table with million rows.. where max will return I/O this can be minimized using order by and inline views — user1954762, Jun 13 '16 at 10:51
How so? You're getting a single aggregate and have to look at all the data (for the specified `a.number`) to find that. What would you group by? Are you - perhaps - trying to show other columns from the tables that have the max `column1` value? — Alex Poole, Jun 13 '16 at 10:56
yes Alex I need to show column from other table that have the max column value — user1954762, Jun 13 '16 at 10:59
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/114516/discussion-between-user1954762-and-alex-poole). — user1954762, Jun 13 '16 at 11:01
Edit your question to say what you want, and to show the output you're trying to get. — Alex Poole, Jun 13 '16 at 11:08

score 0 · Answer 1 · answered Jun 13 '16 at 11:20

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

score 0 · Accepted Answer · answered Jun 13 '16 at 11:40

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:

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

Restructure Inline view

2 Answers2