Geek asks and answers

This statement is shorter / order

Is it possible to write this statement shorter?

Select S_name 
from 
  Supplier
  JOIN Nation ON Supplier.S_nationkey = Nation.N_nationkey 
  JOIN Region on Nation.n_regionkey = region.R_regionkey 
Where
  Region.r_name = 'AFRICA' 
Union
Select C_name
from 
  Customer 
  JOIN Nation ON Customer.C_Nationkey = Nation.N_nationkey 
  JOIN Region on Nation.N_regionkey = Region.R_regionkey 
Where
  Region.R_name = 'AFRICA'

and I want to order my output by name, but I don’t know why, because I have C_name and S_Name as Output ?!

thank

+3
source share
2 answers

If you need all the data in one column, you can place around it SELECTand then do it ORDER BY.

    Select S_name As Names
    from Supplier 
    JOIN Nation 
        ON Supplier.S_nationkey = Nation.N_nationkey 
    JOIN Region 
        on Nation.n_regionkey = region.R_regionkey 
    Where Region.r_name = 'AFRICA' 
    Union
    Select C_name As Names
    from Customer 
    JOIN Nation 
        ON Customer.C_Nationkey = Nation.N_nationkey 
    JOIN Region 
        on Nation.N_regionkey = Region.R_regionkey 
    Where Region.R_name = 'AFRICA'
    ORDER BY Names

If you do not need data in the same column, you can do it as follows:

Select S_name, c.C_name
from Supplier 
JOIN Nation 
    ON Supplier.S_nationkey = Nation.N_nationkey 
JOIN Region 
    on Nation.n_regionkey = region.R_regionkey 
JOIN Customer c
    on Nation.N_nationkey = c.C_Nationkey
Where Region.r_name = 'AFRICA' 
ORDER BY S_name, c.c_name
+2
source

As for the order by clause, try

ORDER BY 1

at the end of your request

+1
source

More articles:


All Articles