CREATE TABLE plch_sales
(
product VARCHAR2(10)
, country VARCHAR2(10)
, year NUMBER
, sales NUMBER
)
/
BEGIN
INSERT INTO plch_sales VALUES ('BANANA', 'US', 2009, 200);
INSERT INTO plch_sales VALUES ('BANANA', 'US', 2010, 300);
INSERT INTO plch_sales VALUES ('BANANA', 'GB', 2009, 400);
INSERT INTO plch_sales VALUES ('BANANA', 'GB', 2010, 350);
INSERT INTO plch_sales VALUES ('BANANA', 'DK', 2010, 250);
INSERT INTO plch_sales VALUES ('APPLE' , 'US', 2009, 100);
INSERT INTO plch_sales VALUES ('APPLE' , 'GB', 2009, 150);
INSERT INTO plch_sales VALUES ('APPLE' , 'GB', 2010, 150);
INSERT INTO plch_sales VALUES ('APPLE' , 'DK', 2009, 250);
INSERT INTO plch_sales VALUES ('APPLE' , 'DK', 2010, 250);
INSERT INTO plch_sales VALUES ('PEAR' , 'GB', 2010, 150);
INSERT INTO plch_sales VALUES ('PEAR' , 'DK', 2009, 300);
INSERT INTO plch_sales VALUES ('PEAR' , 'DK', 2010, 350);
COMMIT;
END;
/
--Query-1
SELECT CASE GROUPING_ID(s.product, s.country)
WHEN 1 THEN 'Product'
WHEN 2 THEN 'Country'
WHEN 3 THEN 'Grand'
END total
, CASE GROUPING(s.product)
WHEN 1 THEN 'TOTAL'
ELSE s.product
END product
, CASE GROUPING(s.country)
WHEN 1 THEN 'TOTAL'
ELSE s.country
END country
, SUM(s.sales) sales
FROM plch_sales s
GROUP BY GROUPING SETS(
(s.product),
(s.country),
()
)
ORDER BY s.product, s.country;
--Query-2
SELECT CASE GROUPING_ID(s.product, s.country)
WHEN 1 THEN 'Product'
WHEN 2 THEN 'Country'
WHEN 3 THEN 'Grand'
END total
, CASE GROUPING(s.product)
WHEN 1 THEN 'TOTAL'
ELSE s.product
END product
, CASE GROUPING(s.country)
WHEN 1 THEN 'TOTAL'
ELSE s.country
END country
, SUM(s.sales) sales
FROM plch_sales s
GROUP BY GROUPING SETS(
(s.product),
(s.country),
()
)
ORDER BY product, country;Description:
There are two queries Query-1 and Query-2
The difference between them is
Query-1 uses column names with table alias
Query-2 uses column names without table alias
My Question:
When there is only one table in a SQL query then using or not using table alias with column name should not make any difference to the output, but here I am getting different outputs can you please tell me why this is happening, as far as I know that when we have two tables which are having similar column names then we have to use table alias to explicitly tell the Oracle which table's column we are referring to but if there is only a single table in the query then we may or may not use tables alias with the columns this won't change the output however this assumption is getting failed in above mentioned case. Kindly guide me what is wrong with my understanding about this concept.
In ORDER BY you can refer to column aliases in the SELECT clause.
This is what's happening in query 2. It isn't sorting by the values in the column. It's sorting by the values from the product and country
expressions (CASE GROUPING ...).
So for product TOTALs it sorts the values DK, GB, US, TOTAL => DK, GB, TOTAL, US
Whereas adding the table alias in query 1 means the database sorts using the
column values.
These are null for the rows displaying TOTAL for product and country. So for product TOTALs it sorts NULL, GB, US, DK. The default sorting is NULLS LAST. So this becomes DK, GB, US, NULL.
Perhaps this example makes it clearer:
with rws as (
select level rn
from dual
connect by level <= 10
)
select mod ( rn, 3 ) rn,
mod ( rn, 10 ) mod10
from rws r
order by rn, -- sort by mod ( rn, 3 )
r.rn desc; -- sort by rws.rn descending
RN MOD10
0 9
0 6
0 3
1 0
1 7
1 4
1 1
2 8
2 5
2 2