ORDER BY is used in Oracle database to sort the query results, while RANK is an analytical function used to assign a unique ranking to each row of data.

In Oracle database, there are two commonly used sorting functions: ORDER BY and RANK. These two functions are used to sort the query results, but they have different use cases and sorting methods. Below, we will provide detailed explanations of how to use these two functions and their differences.

1. ORDER BY

ORDER BY is a clause in SQL used to sort the query results. It can sort the results in ascending (ASC) or descending (DESC) order based on one or more columns.

Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Example:

Assuming there is a table named "employees" with columns "id", "name", "age", and "salary", we can use ORDER BY to sort the query results.

Sort by age in ascending order:

SELECT * FROM employees
ORDER BY age;

Sort by salary in descending order:

SELECT * FROM employees
ORDER BY salary DESC;

Sort by age in ascending order and then by salary in descending order:

SELECT * FROM employees
ORDER BY age, salary DESC;

2. RANK

RANK is an analytical function used to assign a unique ranking to each row of the query results. When two rows have the same value, they will get the same ranking and the next ranking will be skipped.

Syntax:

SELECT column1, column2, ..., RANK() OVER (ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...)
FROM table_name;

Example:

Assuming there is a table named "employees" with columns "id", "name", "age", and "salary", we can use the RANK function to assign rankings to the query results.

Assign rankings to rows based on salary in descending order:

SELECT id, name, age, salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

In this example, the query results will include all columns from the original table, as well as a new column named "rank" representing the salary ranking of each employee.

Summary

ORDER BY is a clause in SQL used to sort the query results.

RANK is an analytical function used to assign a unique ranking to each row of the query results.

ORDER BY can sort the query results in ascending or descending order based on one or more columns.

RANK assigns a unique ranking to each row of the query results. When two rows have the same value, they will get the same ranking and the next ranking will be skipped.

Thank you for reading! Feel free to leave a comment, follow, like, or express your appreciation for this article. We appreciate your support!