SAP HANA: ORDER BY, GROUP BY and HAVING Clause

SQL ORDER BY Statement

The ORDER BY clause is used in a SELECT statement to sort results either in ascending or descending order. SAP HANA sorts query results in ascending order by default.

Order by should appear at the end of your select statement after your WHERE, GROUP BY and HAVING clauses if any or all of them exist.

Syntax for using SQL ORDER BY clause to sort data is:

SELECT column-list

FROM table_name [WHERE condition]

[ORDER BY column1 [, column2, .. columnN] [DESC or ASC]];

Example: Employee Table

ID	NAME	DEPT	AGE	SALARY	LOCATION
100	Kabil	Electrical	24	25000	Coimbatore
101	Nazeer	Electronics	28	35000	Coimbatore
102	Aravind	Aeronautics	28	35000	Chennai
103	Kalai	Electronics	22	20000	Coimbatore
104	Keerthi	InfoTech	25	30000	Bangalore

For Example:

If you want to sort the employee table by the salary of the employee, the SQL query would be.

CODE 1:

Select "NAME","SALARY" FROM "KABIL_PRACTICE"."DEMO_EMP" ORDER BY "SALARY";

Result:

NAME	SALARY
Kalai	20000
Kabil	25000
Keerthi	30000
Nazeer	35000
Aravind	35000

CODE 2:

Select "NAME","SALARY" FROM "KABIL_PRACTICE"."DEMO_EMP" ORDER BY 1;

Then the above code “1” indicates the 1^st Column which we used in our select statement. Here I used “NAME” as a 1^st column. So, the results set order by Name.

Result:

NAME	SALARY
Aravind	35000
Kabil	25000
Kalai	20000
Keerthi	30000
Nazeer	35000

CODE 3:

Select "NAME","SALARY" FROM "KABIL_PRACTICE"."DEMO_EMP" ORDER BY "NAME","SALARY";

The query first sorts the result according to name and then displays it. You can also use more than one column in the ORDER BY clause. If you want to sort the employee table by the name and salary.

Result:

NAME	SALARY
Aravind	35000
Kabil	25000
Kalai	20000
Keerthi	30000
Nazeer	35000

To Sort Data in Descending Order

By default, the ORDER BY Clause sorts data in ascending order. If you want to sort the data in descending order, you must explicitly specify it as shown below.

CODE 4:

Select "NAME","SALARY" FROM "KABIL_PRACTICE"."DEMO_EMP" ORDER BY "NAME","SALARY" desc;

The above query sorts only the column 'salary' in descending order and the column 'name' by ascending order.

Result:

NAME	SALARY
Aravind	35000
Kabil	25000
Kalai	20000
Keerthi	30000
Nazeer	35000

If you want to select both name and salary in descending order, the query would be as given below.

CODE 5:

Select "NAME","SALARY" FROM "KABIL_PRACTICE"."DEMO_EMP" ORDER BY "NAME" desc,"SALARY" desc;

Result:

NAME	SALARY
Nazeer	35000
Keerthi	30000
Kalai	20000
Kabil	25000
Aravind	35000

How to use expressions in the ORDER BY Clause?

Expressions in the ORDER BY clause of a SELECT statement.

For example:

If you want to display employee name, current salary, and a 20% increase in the salary for only those employees for whom the percentage increase in salary is greater than 30000 and in descending order of the increased price, the SELECT statement can be written as shown below

CODE 6:

Select "NAME","SALARY","SALARY" * 1.2 as NEW_SALARY FROM "KABIL_PRACTICE"."DEMO_EMP" Where "SALARY" * 1.2 > 30000 ORDER BY NEW_SALARY desc;

Result:

NAME	SALARY	NEW_SALARY
Nazeer	35000	42000
Aravind	35000	42000
Keerthi	30000	36000

SQL GROUP BY Clause

The SQL GROUP BY Clause is used along with the group functions to retrieve data grouped according to one or more columns.

The GROUP BY clause is a SQL command that is used to group rows that have the same values.

The queries that contain the GROUP BY clause are called grouped queries and only return a single row for every grouped item.

For Example:

If you want to know the total amount of salary spent on each department, the query would be:

CODE 1:

Select "DEPT", SUM("SALARY") from "KABIL_PRACTICE"."DEMO_EMP" Group by "DEPT";

Result:

DEPT	SUM(SALARY)
Electrical	25000
Electronics	55000
Aeronautics	35000
InfoTech	30000

NOTE:

The group by clause should contain all the columns in the select list expect those used along with the group functions.

CODE 2:

Select "LOCATION","DEPT", SUM("SALARY") from "KABIL_PRACTICE"."DEMO_EMP" Group by "LOCATION","DEPT";

Result:

LOCATION	DEPT	SUM(SALARY)
Coimbatore	Electrical	25000
Coimbatore	Electronics	55000
Chennai	Aeronautics	35000
Bangalore	InfoTech	30000

SQL GROUP BY with HAVING Clause

Having clause is used to filter data based on the group functions. This is similar to WHERE condition but is used with group functions. Group functions cannot be used in WHERE Clause but can be used in HAVING clause.

For Example:

If you want to select the department that has total salary paid for its employees more than 30000, the SQL query would be like;

CODE 1:

Select "DEPT",SUM("SALARY") from "KABIL_PRACTICE"."DEMO_EMP" Group by "DEPT" Having SUM("SALARY") > 30000 ;

Result:

DEPT	SUM(SALARY)
Electronics	55000
Aeronautics	35000

Note:

We can use Order by at the end of the above code:

CODE 2:

Select "DEPT", SUM("SALARY") from "KABIL_PRACTICE"."DEMO_EMP" Group by "DEPT" Having SUM("SALARY") > 30000 order by "DEPT";

Result:

DEPT	SUM(SALARY)
Aeronautics	35000
Electronics	55000

Note:

When WHERE, GROUP BY and HAVING clauses are used together in a SELECT statement, the WHERE clause is processed first, then the rows that are returned after the WHERE clause is executed are grouped based on the GROUP BY clause.

Finally, any conditions on the group functions in the HAVING clause are applied to the grouped rows before the final output is displayed.

Thank you for visiting my Blog...

Share your Comments...