SQL ORDER BY Clause

The SQL ORDER BY keyword is used to sort the query results in ascending or descending order based on the columns(one or more) that you specify to order by.

It is used with SQL Select statement for arranging query result in particular order.

To sort query results in descending order, We can use DESC keyword with sql Order by clause.

Use the alias name of the column mentioned in the SELECT statement in the ORDER BY clause of the query.

A column alias name cannot be ordered explicitly. In the select list, you must specify the column position of the column aliased by the name.

Example 1: query processing order, alias can be used in order by.

SELECT DisplayName, JoinDate as jd, Reputation as rep
FROM Users
ORDER BY jd, rep

In the select statement, you can use the relative order of the columns. Consider the same example as before, but instead of aliases, use relative order, such as 1 for display name, 2 for Jd, and so on.

SELECT DisplayName, JoinDate as jd, Reputation as rep
FROM Users
ORDER BY 2, 3

Example 2: Suppose you have the following base table definition:

CREATE TABLE t1
(i INTEGER,
j INTEGER,
k INTEGER);

The following query against this table fails because it specifies ordering on the column alias name j, which the system interprets as column j in table t1 rather than as the expression SUM(t1.j):

SELECT t1.i, SUM(t1.j) AS j, t1.k
FROM t1
GROUP BY 1,3
ORDER BY j;

*** Failure 3504 Selected non-aggregate values must be part of the associated group.

Example 3:

Step 1: DECLARE @Tmp TABLE (Id INT)

Step 2: Insert records

INSERT @Tmp SELECT 3
INSERT @Tmp SELECT 2
INSERT @Tmp SELECT 1

Step 3: Without ORDER BY

SELECT * FROM @Tmp

Step 4: With ORDER BY AliasName

SELECT Id AliasName
FROM  @Tmp
ORDER BY AliasName

Example 4: This SQL sort Desc example use the Alias Column Name to arrange the Data in the Descending result set:

SELECT [EmpID]
      ,[FirstName] + ' ' + [LastName] AS [Full Name]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  ORDER BY [Full Name] DESC

[Full Name] was created by combining the [FirstName] and [LastName] fields. Following that, we used the Alias name. It signifies that data will be sorted in ascending order by [Full Name].

The SQL ORDER BY command sorts the obtained data in ascending or descending order based on one or more columns.

Make sure that whichever column you're using to sort is included in the column-list.

The ORDER BY clause in SQL is used to sort the records in a SELECT statement's result set.

As a rule, The data is sorted in ascending order using ORDER BY.

We can sort the data in descending order with the phrase DESC and ascending order with the term ASC.

Syntax:

SELECT column-list 
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

Parameter Explaination:

• table_name: name of the table.
• column_name: name of the column according to which the data is needed to be arranged.
• ASC: Optional. ASC arranges the result set by expression in ascending order. If no modifier is specified, this is the default behavior.
• DESC: Optional. DESC sorts the result set in descending order by expression.
• | : use either ASC or DESC to sort in ascending or descending order

Note: If the ORDER BY clause does not include the ASC or DESC modifiers, the results will be sorted by phrase in ascending order. This is the same as the ASC ORDER BY phrase.

Example 1: CUSTOMERS table having the following records:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The following code block has an example, which would sort the result in the descending order by NAME.

SELECT * FROM CUSTOMERS
ORDER BY NAME DESC;

Output:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
+----+----------+-----+-----------+----------+

Example 2: In this example, we'll get all of the data from the Student table and sort it by the column ROLL_NO in decreasing order.

SELECT * FROM Student ORDER BY ROLL_NO DESC;

Example 3: Sorting Results in descending order:

You utilize the DESC property in your ORDER BY clause to sort your result set in descending order.

In this example, we have a table called suppliers with the following data:

supplier_id	supplier_name		city		    	state
100		    Microsoft		    Redmond		    	Washington
200		    Google			    Mountain View	    California
300		    Oracle			    Redwood City	    California
400		    Kimberly-Clark		Irving			    Texas
500		    Tyson Foods		    Springdale		    Arkansas
600		    SC Johnson		    Racine			    Wisconsin
700		    Dole Food Company	Westlake Village	California
800		    Flowers Foods		Thomasville		    Georgia
900		    Electronic Arts		Redwood City		California

SQL QUERY:

SELECT *
FROM suppliers
WHERE supplier_id > 400
ORDER BY supplier_id DESC;

Output:

supplier_id	supplier_name		city			    state
900	    	Electronic Arts		Redwood City		California
800		    Flowers Foods		Thomasville		    Georgia
700	    	Dole Food Company	Westlake Village	California
600		    SC Johnson	    	Racine			    Wisconsin
500		    Tyson Foods		    Springdale		    Arkansas

This example would sort the result set by the supplier_id field in descending order.

Example 4: Let's see an example of an employee table:

ID	NAME		    AGE	ADDRESS		SALARY
1	Himani gupta	21	Modinagar	22000
2	Shiva tiwari	22	Bhopal		21000
3	Ajeet bhargav	45	Meerut		65000
4	Ritesh yadav	36	Azamgarh	26000
5	Balwant singh	45	Varanasi	36000
6	Mahesh sharma	26	Mathura		22000

To sort the result in descending order by NAME :

SELECT * FROM CUSTOMERS  
ORDER BY NAME DESC;  

Output:

ID	NAME    	AGE	ADDRESS	    SALARY
2	Shiva tiwari	22	Bhopal	    21000
4	Ritesh yadav	36	Azamgarh	26000
6	Mahesh sharma	26	Mathura	    22000
1	Himani gupta	21	Modinagar	22000
5	Balwant singh	45	Varanasi	36000
3	Ajeet bhargav	45	Meerut	65000

The ORDER BY clause might also include the CASE statement. The ORDER BY clause in SQL is used to sort the results either ascending or descending.

You can use ORDER BY with basic and searching CASE statements in addition to sorting rows using one or more columns.

Example 1: Continuing with the sales manager's request, let's say she also wants the products categorised by price range and then by product name.

We can use CASE as one of the values from which to arrange the results because it is an expression. Understand that sorting isn't restricted to table columns; we can also sort an expression.

The following query sorts by price range as a column.

SELECT Name,
         ListPrice
FROM     Production.Product
ORDER BY CASE
            WHEN ListPrice = 0 THEN 'No Price'
            WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
            WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
            WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
            WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
            WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
            WHEN ListPrice > 2000 THEN 'Luxury'
            ELSE 'UNLISTED'
         END,
         Name

Example 2: SELECT with ORDER BY CASE:

The ORDER BY and CASE statements are used in combination in the following SQL query.

SELECT * FROM Country
ORDER BY CASE WHEN cname='other' THEN 1 ELSE 0 END

Output:

ID	cname
1	Afghanistan
2	Australia
3	France
4	Oman
6	Singapore
7	United States
5	Other

The ORDER BY clause combined with CASE moves the "Other" option to the bottom. This reasoning can be used for other things besides country, such as university name, city, state, and so on.

Example 3: we use the CASE after ORDER BY and then checks for column value.

SELECT * FROM PersonalDetails
ORDER BY
       CASE Active WHEN 1 THEN Active END ASC,
       CASE WHEN Active = 0 THEN LastName
       ELSE FirstName END DESC

In above case, all records having Active = 1 is sorted on “Active ASC” order. All records having Active = 0 is sorted on ‘LastName DESC’ else ‘FirstName DESC’ order.

Example 4: The following example demonstrates how to sort data using CASE. Consider retrieving data from the furniture data database, but only the rows with an even number of points should be shown at the top of the results. This cannot be done using columns as sorting criterion; CASE is required.

Below is an example of the relevant code:

SELECT id, furniture, points
FROM furniture _data
ORDER BY CASE 
              WHEN points%2=0  THEN 1
              ELSE 2
         END ;

Output:

id	furniture	points
2	sofa		8
3	table		2
1	chair		5
4	bookcase	5
5	bed		3

What did the CASE expression do to accomplish this? It looked at whether the number in the points column could be divided by two without leaving a remainder. If it could, a 1 was returned, and the row was displayed in the results' top section.

If the order criterion comprises an aggregate function, another option is to use column numbers instead of column names. (An alternate method is to use column titles, which are then used in the ORDER BY clause.) However, using column names rather than numbers in the ORDER BY clause is recommended to make it easier to maintain the query if any columns in the SELECT list need to be added or removed.

The index number for a column can be used with the GROUP BY clause to sort the results of a query in descending order based on that column.

Example 1: For each project number, get the project number and the number of all employees, in descending order of the employee number:

USE sample;

SELECT project_no, COUNT(*) emp_quantity
	FROM works_on
	GROUP BY project_no
	ORDER BY 2 DESC

Output:

project_no	emp_quantity
p1		4
p2		4
p3		3

If the order is ascending, NULL values appear at the top of the list, and if the order is descending, NULL values appear at the bottom of the list.

Example 2: calculations in the ORDER BY, You don’t even have to put column names.

SELECT LoginID, YEAR(HireDate) AS HireYear, 
    MONTH(HireDate) AS HireMonth
FROM HumanResources.Employee
ORDER BY 2, 3, 1

Each number represents a position in the field list. 1=’LoginID’, 2=’YEAR(HireDate)’ etc.

Example 3: The following SQL statement may be used to extract the number of agents for each group of 'working area' and the number of unique 'commission' for each group of 'working area' in an organised order on column number 1. number of agents for each group of 'working area' from the stated column list from the 'agents' table:

SELECT COUNT(agent_name), working_area,
COUNT(DISTINCT commission)
FROM AGENTS
GROUP BY working_area
ORDER BY 1;

Output:

COUNT(AGENT_NAME) WORKING_AREA                        COUNT(DISTINCTCOMMISSION)
----------------- -------------			      -------------------------
                1 Torento                                                     1
                1 San Jose                                                    1
                1 New York                                                    1
                1 Chennai                                                     1
                1 Hampshair                                                   1
                1 Mumbai                                                      1
                1 Brisban                                                     1
                2 London                                                      2
                3 Bangalore                                                   2

The ORDER BY COUNT clause in SQL is used to sort the result set returned by a SELECT query in ascending or descending order depending on values returned by the COUNT function.

The COUNT() function is used to calculate the total number of records in the result set for the uninitiated. It's typically combined with the GROUP BY clause to generate a summary of the total number of records in each group. It can also order the obtained summary table using the ORDER BY clause.

The ORDER BY command sorts the results in ascending order by default. If we want to sort it in decreasing order, we may need to use the DESC keyword individually.

Syntax:

The basic syntax used for writing SELECT query with ORDER BY COUNT() clause is as follows:

SELECT column_name_1, column_name_2
FROM
table_name
GROUP BY column_name_1
ORDER BY COUNT(column_name_2) ASC | DESC;

Example 1: Our database has a table named user with data in the following columns: id, first_name, last_name, and country.

id	first_name	last_name	country
1	Lisa		Williams	England
2	Gary		Anders		Poland
3	Tom		Williams	Poland
4	Michael		Brown		France
5	Susan		Smith		USA
6	Anne		Jones		USA
7	Ellie		Miller		Poland

Let's put together a user report. We'll sort the findings by nation and count how many people came from each one. However, we'll rank the groupings by number of users in descending order. As a result, the countries with the most users will show at the top.

SELECT country,
 COUNT(id)
FROM user
GROUP BY  country
ORDER BY COUNT(id) DESC ;

Output:

country		count(id)
Poland		3
USA		2
England		1
France		1

Example 2: Find the number of sales made by each salesperson and arrange from lowest to highest:

SELECT salesperson, count(product_id)
FROM product_details
GROUP BY salesperson
ORDER BY count(product_id);

The query first groups the results by salesperson, then counts the number of product_ids corresponding to each group and finally sorts the result set according to the value returned by the count() function.

Example 3: You need to aggregate the data first, this can be done using the GROUP BY clause:

SELECT Group, COUNT(*)
FROM table
GROUP BY Group
ORDER BY COUNT(*) DESC

The DESC keyword allows you to display the highest count first, whereas ORDER BY, by default, displays the lowest count first.

ORDER BY is a SQL clause that is used in conjunction with a SELECT query to retrieve records from a table in ascending or descending order.

We may sort the dates recorded in the SQL table's column in the same way we can sort the integer and string values placed in the column of the tables.

By default, all of the records will be arranged ascending. The DESC keyword is used to sort the records in descending order, and the same procedure is applied to the date.

Example 1 : To further comprehend this concept, consider a few practical instances. All queries will be written in the MySQL database.

Consider we have created a table named as employees in MySQL database with the following data:

ID	Name		Salary	Joining_Date	DOB
1	Rohit More	50000	2020-02-08	1991-01-28 18:06:08
2	Kunal Mohite	34000	2021-01-01	1990-05-15 19:10:00
3	Saurabh Jha	61000	2015-05-01	1983-02-20 12:18:45
4	Anant Desai	59000	2018-08-27	1978-06-29 15:45:13
5	Krishna Sharma	48000	2010-10-23	1999-03-21 02:14:56
6	Bhavesh Jain	37000	2021-07-03	1998-08-02 13:00:01

Create a query to present all employee information in ascending order by date of birth.

SELECT *FROM employees ORDER BY DOB;  

We used the ORDER BY clause on the column 'DOB' because we wanted to sort the data by employee date of birth in ascending order.

Output:

ID	Name		Salary	Joining_Date	DOB
4	Anant Desai	59000	2018-08-27	1978-06-29 15:45:13
3	Saurabh Jha	61000	2015-05-01	1983-02-20 12:18:45
2	Kunal Mohite	34000	2021-01-01	1990-05-15 19:10:00
1	Rohit More	50000	2020-02-08	1991-01-28 18:06:08
6	Bhavesh Jain	37000	2021-07-03	1998-08-02 13:00:01
5	Krishna Sharma	48000	2010-10-23	1999-03-21 02:14:56

The records are shown in ascending order of the DOB, according to the results of the previous query.

Example 2: Subject, exam_year, exam_month, and exam_day are the columns in the exam table. The months are listed alphabetically rather than numerically.

subject		exam_year	exam_month	exam_day
Mathematics	2019		December	19
English		2020		January		8
Science		2020		January		5
Health		2020		January		5
Art		NULL		NULL		NULL

To sort the rows by exam date:

SELECT *
FROM exam
ORDER BY STR_TO_DATE(CONCAT(exam_year, ' ', exam_month, ' ', exam_day), '%Y %M %d');

The result looks like this (the rows are sorted in ascending order by exam_year, exam_month, and exam_date):

subject		exam_year	exam_month	exam_day
Art		NULL		NULL		NULL
Mathematics	2019		December	19
Health		2020		January		5
Science		2020		January		5
English		2020		January		8

Example 3: sort date and time in descending order:

select UserId,UserName,date(IssueDate) as date1,
IssueTime from SortByDateAndTime
order by date(IssueDate)desc,IssueTime desc;

Output

+--------+----------+------------+-----------+
| UserId | UserName | date1      | IssueTime |
+--------+----------+------------+-----------+
|      1 | John     | 2018-12-16 | 10:30:00  |
|      3 | Carol    | 2018-12-16 | 10:20:00  |
|      2 | Bob      | 2018-12-16 | 10:10:00  |
|      4 | Sam      | 2018-12-16 | 10:00:00  |
+--------+----------+------------+-----------+
4 rows in set (0.00 sec)

This output displaying in sorted date and time.

Or you can use another query to sort date and time. The query is as follows:

select UserId,UserName,date(IssueDate) as date1,
IssueTime from SortByDateAndTime
order by date(IssueDate) desc,IssueTime asc;

Output:

+--------+----------+------------+-----------+
| UserId | UserName | date1      | IssueTime |
+--------+----------+------------+-----------+
|      4 | Sam      | 2018-12-16 | 10:00:00  |
|      2 | Bob      | 2018-12-16 | 10:10:00  |
|      3 | Carol    | 2018-12-16 | 10:20:00  |
|      1 | John     | 2018-12-16 | 10:30:00  |
+--------+----------+------------+-----------+

The DISTINCT does not need ORDER BY. The solution is straightforward. When you use the DISTINCT column in a query, the result is always sorted by the columns you specified in the DISTINCT column.

There will be an error in the query if you use any other column in the ORDER BY near to the one used in the SELECT statement.

Furthermore, utilising ORDER BY has no negative impact on performance or results. However, the column is already ordered by DISTINCT, therefore adding the order by will be unnecessary.

Example 1:

ORDER BY clause, ordering by the LENGTH column, Separate clause, deleting distinct tuples (with projected LENGTH columns)

SELECT DISTINCT length FROM film ORDER BY length

Output:

1
length |
2
-------|
3
46     |
4
47     |
5
48     |
6
49     |
7
50     |
8
51     |
9
52     |
10
53     |
11
54     |
12
55     |
13
56     |
14

Example 2: see only DISTINCT letters, ordered by value. Clearly, this means we want to return:

A
C
B

"A" has the lowest values of 1, followed "C" with a value of 2, and finally "B" with values of 3.

The Query will be like this:

select distinct letter
from x
order by Value

Remember, the "rules" of what is allowed and not allowed in SQL is not determined by how your data looks at a particular point in time; it is a constant. Just because one set of data seems to be perfectly orderable in this scenario does not mean that all data is always going to be perfectly orderable as well. What if we had this set of data instead:

Output:

Letter  Value
—-    —–   
A       1
A       1
B       3
B       0
C       2

Consider the above and consider what should be returned when requesting separate Letters arranged by Value.

Example 1: The issue here is that empty string 'position' entries are regarded as 0. As a result, all entries with an empty string for position appear before those with 1,2,3,4.

eg: '', '', '', 1, 2, 3, 4

I assume the solution may have some kind of replace function but I am not able to find a function which does what I am after.

SELECT * 
FROM tablename 
WHERE visible=1 
ORDER BY 
    case when position in('', '0') then 1 else 0 end,
    position ASC, 
    id DESC

Example 2: which works quite awesome, yet is there any way that I can set a specific value to be last? Such as an empty string (@EMPTY).

...ORDER BY name DESC (@EMPTY) LAST

I'm dynamically constructing SQL based on user choices on a table, and as the user changes the sort order, I update my SQL. Additionally, the user has the option of selecting which column to sort by. Type text or type int will be the column by which I shall sort. When the column is of type int, I don't need any fancy ORDER BY, but when the column is of type text, I do need to force all values of "" to the end of the ORDER BY.

You can use a case as the first element in the sort order:

order by
    case when name = '' then 1 else 0 end
,   name desc

GROUP BY and ORDER BY are both clauses (or statements) that sort query results in the same way. Each of these, however, has a distinct purpose; in fact, they can be used independently or in combination.

In SQL, Group By is used to group comparable data while Order By is used to sort the data in ascending or descending order.

The ORDER BY clause sorts the query results according to one or more columns. Meanwhile, aggregate methods like COUNT(), AVG(), MIN(), and MAX() are utilized in the GROUP BY clause to group data into groups. It works like this: if a column has the same values in different rows, it will group them together.

Example 1: Records are ordered by the actor_id column in the query below, which is how the results are categorised. We'd have to add a Sort BY clause if we wanted to order results using different fields, such as non-grouped fields. The same query, but this time sorted by how many films each actor has appeared in, from most to least:

SELECT count(film_actor.actor_id) AS num_of_films_appeared_in, actor.actor_id,
actor.first_name,
actor.last_name
FROM actor INNER JOIN film_actor ON actor.actor_id = film_actor.actor_id
GROUP BY film_actor.actor_id
ORDER BY film_actor.actor_id, last_name, first_name DESC;

Example 2: To get 'agent_code' and 'agent_name' columns from the table 'agents' and sum of 'advance_amount' column from the table 'orders' after a joining, with following conditions:

'agent_code' of 'agents' and 'orders' must be same, the same combination of 'agent_code' and 'agent_name' of 'agents' table must be within a group, 'agent_code' of 'agents' table should arrange in an order, default is ascending order, the following SQL statement can be used:

SELECT agents.agent_code,agents.agent_name,
SUM(orders.advance_amount)
FROM agents,orders
WHERE agents.agent_code=orders.agent_code
GROUP BY agents.agent_code,agents.agent_name
ORDER BY agents.agent_code;

Example 3: usage of GROUP BY and ORDER BY clause within a join.

select l.* 
from table l
inner join (
  select 
    m_id, max(timestamp) as latest 
  from table 
  group by m_id
) r
  on l.timestamp = r.latest and l.m_id = r.m_id
order by timestamp desc

Except if a - (minus) character is included before the column name while sorting, NULL results are considered lower in order in MySQL than any non-NULL value.

If you apply an ORDER BY clause to a column that contains NULL values, the NULL values will appear first or last in the result set. The result is determined on the database type.

PostgreSQL considers NULL values to be greater than non-NULL values by default. If you sort your output in ascending order — either manually or with the ASC keyword.

Example 1: NULL values will be shown last in the output. Here’s an example:

SELECT *
FROM paintings
ORDER BY year;

Output:

id	painting		author			year
4	The Night Watch		Rembrandt		1642
2	The Starry Night	Vincent van Gogh	1889
3	The Scream		Edvard Munch		1893
1	Mona Lisa		Leonardo da Vinci	NULL
5	The Birth of Venus	Sandro Botticelli	NULL

SQLite, in contrast to the other database types, considers NULLs to be smaller than any other value. The NULLs will appear first if you sort a column with NULL values in ascending order.

SELECT *
FROM paintings
ORDER BY year;

Output:

id	painting		author			year
1	Mona Lisa		Leonardo da Vinci	NULL
5	The Birth of Venus	Sandro Botticelli	NULL
4	The Night Watch		Rembrandt		1642
2	The Starry Night	Vincent van Gogh	1889
3	The Scream		Edvard Munch		1893

Example 2:

SELECT * FROM user 
ORDER BY -date_login DESC;

While this method may work well for numbers and dates, it may not be the greatest option for sorting fields containing alpha or alphanumeric values; instead, you should try the other ways outlined in this article.

Output:

+----+--------+------------+
| id |  name  | date_login |
+----+--------+------------+
|  3 |  david | 2016-12-24 |
|  4 |  zayne | 2017-03-02 |
|  1 |  NULL  | 2017-03-12 |
|  2 |  john  | NULL       |
+----+--------+------------+

Example 3: when sorting data with NULL, these NULL values are:

SELECT
first_name, last_name, email_address
FROM customer
ORDER BY email_address;

Output:

FIRST_NAME	LAST_NAME	EMAIL_ADDRESS
Adam		Smith		(null)
John		Cooper		(null)
John		Smith		johnsmith29@abc.com
Mary		Fisher		mf2000@abc.com
Sarah		Smith		ssmith@aol.com

The two rows with NULL are shown at the top of the result, then the email addresses are ordered by J, M, then S.

A subquery cannot utilise an ORDER BY command, while the main query can. In a subquery, the GROUP BY command can accomplish the same function as the ORDER BY command.

Put the ORDER BY inside the subquery to make your application not work (maybe it has an option to not use a needless subquery in the first place). This syntax is not allowed in SQL Server without TOP, as you've already learned. Using TOP 100 PERCENT will also render the ORDER BY optimized away, unless you wish to leave some rows out.

Example 1: move the ORDER BY clause outside of the subquery, like this:

SELECT cat, id
FROM (
	SELECT ROW_NUMBER() OVER (
	PARTITION BY cat ORDER BY id
	) AS GrpID, cat, id
	FROM test
	) x
ORDER BY GrpID, cat

You can think of the subquery as generating a temporary table and then running a select clause on that temp table. So the query above could also be written like this:

SELECT ROW_NUMBER() OVER (
	PARTITION BY cat ORDER BY id
	) AS GrpID, cat, id
INTO #temp
FROM test

SELECT cat, id
FROM #temp
ORDER BY GrpID, cat

It's pointless to use an ORDER BY clause in the subquery because the results are only stored in an internal table and never printed (and if you did have an ORDER BY, it would just slow down the query). Because you're seeking to order the query's resultset, the ORDER BY clause in the outer query is required.

Example 2: Order by value from subquery:

mysql> SELECT   EmployeeNO, AMOUNT
 FROM PENALTIES AS P1
 ORDER BY (SELECT   AVG(AMOUNT)
 FROM PENALTIES AS P2
 WHERE P1.EmployeeNO = P2.EmployeeNO);

Output:

+------------+--------+
| EmployeeNO | AMOUNT |
+------------+--------+
|          8 |  25.00 |
|         44 |  75.00 |
|         44 |  25.00 |
|         44 |  30.00 |
|        104 |  50.00 |
|         27 | 100.00 |
|         27 |  75.00 |
|          6 | 100.00 |
+------------+--------+

Example 1: Query to order by last 3 chars.

Case 1 − Get the result in ascending order.

The query is as follows :

select *from OrderByLast3Chars
order by RIGHT(EmployeeName,3) asc;

Output:

+------------+--------------+-------------+
| EmployeeId | EmployeeName | EmployeeAge |
+------------+--------------+-------------+
|          1 | Carol_901    |          24 |
|          2 | Bob_101      |          21 |
|          3 | Sam_107      |          22 |
|          4 | Mile_677     |          26 |
|          5 | John_978     |          27 |
|          6 | David_876    |          29 |
+------------+--------------+-------------+
6 rows in set (0.00 sec)

Case 2 − Get the result in descending order. The query is as follows:

select *from OrderByLast3Chars
order by RIGHT(EmployeeName,3) desc;

Output:

+------------+--------------+-------------+
| EmployeeId | EmployeeName | EmployeeAge |
+------------+--------------+-------------+
|          5 | John_978     |          27 |
|          1 | Carol_901    |          24 |
|          6 | David_876    |          29 |
|          4 | Mile_677     |          26 |
|          3 | Sam_107      |          22 |
|          2 | Bob_101      |          21 |
+------------+--------------+-------------+
6 rows in set (0.00 sec)

Example 3: right-most 3 characters and ordering by that value ascending.

SELECT *
FROM table_name
ORDER BY RIGHT(name, 3) ASC;

It's worth noting that as your data grows, this approach will become inefficient. You'll probably want to keep the numeric appendix in a separate, indexed integer column in the future so that sorting is as efficient as possible.

When using LIMIT, being able to sort the returned rows is really useful.

MySQL SELECT statement with LIMIT and ORDER BY clause.

After the FROM clause, but before the LIMIT clause, comes the ORDER BY clause.

Syntax:

SELECT expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]]
LIMIT row_count_number;

Parameters:

• expressions: The columns or calculations that you wish to retrieve.
• tables: The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
• WHERE conditions: These are optional conditions that must be met for the records to be selected.
• ORDER BY expression: These are optional statements used to return the result in ascending or descending order.
• LIMIT row_count_number: Specifies a limited number of rows to be returned based on row_count_number.

Example 1: We can retrieve limited rows from the database. I can be used in pagination where are forced to show only limited records like 10, 50, 100 etc.

You must utilize ROWNUM queries if you wish to use the LIMIT clause with SQL because it is used after the results have been selected.

SELECT name, age  
FROM   
(SELECT name, age, ROWNUM r  
FROM   
(SELECT name, age, FROM employee_data  
ORDER BY age DESC  
)  
WHERE ROWNUM <=40  
)  
WHERE r >= 21;  

This query will give you 21th to 40th rows.

Example 2: This example query returns name in reverse-alphabetical order and shows only the top 3 results:

SELECT name
FROM baby_names
ORDER BY name DESC
LIMIT 3;

Output:

name
Zzyzx
Zyyon
Zyyanna

Example 3: The LIMIT operator can be used in instances like the one above, when we don't want to use any conditional statements and need to discover the top two students with the highest scores. ORDERING BY SCORING DESC sorted the records in descending order, and we got the first two rows from the sorted results using LIMIT 2.

In the aforementioned example, the WHERE clause can also be used to include particular scenarios. Assume we don't want the Civil branch in our result set and just want the first two students to receive bad grades.

Query:

SELECT * FROM Student WHERE Branch != 'Civil' ORDER BY Score  LIMIT 2;

In the ORDER BY clause, separate your numerous column names with a comma (,). You can also select whether you want to sort in ASC or DESC order.

Example 1: Ordering data by multiple columns:

You may also sort by a number of columns. This is especially handy if your data is organized into categories and you want to organize rows by date, but retain all of the results within each category together. This query puts the most recent years first, yet top-ranking songs come before lower-ranking tracks:

SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year_rank <= 3
ORDER BY year DESC, year_rank

Example 2: SQL SORTING ON MULTIPLE COLUMNS:

Let's take an example of customer table which has many columns, the following SQL statement selects all customers from the table named "customer", stored by the "country" and "Customer-Name" columns:

SELECT * FROM customers  
ORDER BY country, Customer-Name;  

Example 3: Order by firstname (ascending), lastname(descending), age(descending) then the query is:

SELECT * FROM geeksforgeeks ORDER BY FIRSTNAME, LASTNAME DESC, AGE DESC ;

Example 4:

SELECT * FROM emp_salary ORDER BY age ASC, salary DESC

From above query, I am ordering the emp_salary table by age in Ascending order and salary by descending order.

When NULL values appear in the column on which the result is sorted, the SQL standard does not specify the order of the rows.

To control whether nulls appear before or after non-null entries in the sort order, use the NULLS FIRST and NULLS LAST options. Null values sort as though they are greater than any non-null value by default; that is, NULLS FIRST is the default DESC order, and NULLS LAST is the default otherwise.

Syntax:

SELECT select_list
   FROM table_expression
   ORDER BY sort_expression1 [ASC | DESC] [NULLS { FIRST | LAST }]
   [, sort_expression2 [ASC | DESC] [NULLS { FIRST | LAST }] ...]

Note that the ordering options are considered independently for each sort column. For example ORDER BY x, y DESC means ORDER BY x ASC, y DESC, which is not the same as ORDER BY x DESC, y DESC.

Example 1: sort_expression can instead be the name or number of an output column, as in:

SELECT a + b AS sum, c FROM table1 ORDER BY sum;
SELECT a, max(b) FROM table1 GROUP BY a ORDER BY 1;

Example 2: using last method:

SELECT *
FROM person
ORDER BY middle_name NULLS LAST

Example 3: NULL values are treated as the lowest values by SQL Server. When sorted in ascending order, NULLs, for example, come first.

SELECT value
FROM test
ORDER BY value ASC

The fourth card has the highest limit (infinity) but it was wrongly returned last.

In Oracle or PostgreSQL you could write:

SELECT *
FROM creditCard
ORDER BY monthlyLimit DESC NULLS LAST

Using the RANDOM function in the database, select random rows from the result set using the SQL ORDER BY clause.

This is a great trick to know, especially when you need to rearrange a batch of results.

It's worth noting that sorting a large result set with the RANDOM function might be time-consuming, so practise on tiny sets first.

Syntax:

Select a random row with MySQL:

If you want to return a random row with MY SQL, use the following syntax:

SELECT column FROM table ORDER BY RAND () LIMIT 1;

Select a random row with Postgre SQL:

SELECT column FROM table ORDER BY RANDOM () LIMIT 1;  

Select a random row with SQL Server:

SELECT TOP 1 column FROM table ORDER BY NEWID ();   

Example 1: Selecting random row with SQL Server

SELECT column FROM table 
ORDER BY NEWID ();

Selecting random row with MYSQL

SELECT column FROM table 
ORDER BY RAND () ;

Selecting random row with Postgre SQL

SELECT column FROM table 
ORDER BY RANDOM () ;

Example 2: A 16-byte GUID is returned by the NEWID function as a uniqueidentifier data type. As a result, each new value produced by the NEWID method will be almost unique.

SQL Server

On SQL Server, you need to use the NEWID function, as illustrated by the following example:

SELECT
    CONCAT(CONCAT(artist, ' - '), title) AS song
FROM song
ORDER BY NEWID()

PostgreSQL

On PostgreSQL, you need to use the random function, as illustrated by the following example:

SELECT
    artist||' - '||title AS song
FROM song
ORDER BY random()

The random function returns a double precision numeric result in the [0, 1] interval.

MySQL

On MySQL, you need to use the RAND function, as illustrated by the following example:

SELECT
  CONCAT(CONCAT(artist, ' - '), title) AS song
FROM song
ORDER BY RAND()

The RAND function of a floating-point type returns a numeric result in the [0, 1] interval.

Note: Because the ORDER BY clause uses the random() function call, the songs are listed in random order in the previous example.

The number of matches for the provided regex is returned by regexp.

Example 1: The following sentence utilises the REGEXP_MATCHES() function to find films with the words Cat or Dog in their descriptions.

SELECT film_id, 
	title , 
	description, 
	REGEXP_MATCHES(description, 'Cat | Dog ') cat_or_dog
FROM 
	film
ORDER BY title;	

Example 2: I'd like to reorder that ignoring the leading "The".

SELECT *
FROM table1
ORDER BY REPLACE( UPPER(fieldname), 'THE ', '' );

Example 3: So, this:

order by `bigDataField` regexp 'c' desc

will sort your data by the bigDataField with the most c's in it first, which I assume isn't what you want. To examine the length of the pattern matching, utilize numerous CASE-WHENs (warning: bad performance - not recommended for big tables).

Example 1: Even if a field(s) does not appear in your select statement but does exist in your table, you can order by them. However, for a group by clause, it must be in your choose statement.

The fields used in the GROUP BY clause must be included in the select list when using SELECT DISTINCT.

SELECT F1, F2, F3 from table ORDER BY F4 asc

Example 2: Order by query looks like SQL Server:

Select top 3500 a.customer_no 
From T_CUSTOMER a  WITH (NOLOCK) JOIN
 (Select a1.customer_no
 From VXS_CUST_TKW a1 WITH (NOLOCK)
 Where a1.tkw in (141)
 ) e
   ON e.customer_no = a.customer_no
  Where 1 = 1
  group by a.customer_no
  order by max(a.create_dt) desc;

The ORDER BY clause in MySQL makes it very simple to arrange the results in ascending or descending order. However, there are situations when you need to sort the data in a precise order that the ASC or DSC cannot accomplish. To sort the results in a given order, utilise the FIELD() function in the MySQL ORDER BY clause.

We may accomplish this by using the FIELD() function in the ORDER BY clause. It works by first supplying the column to sort by, followed by the values to sort by.

Example 1: use 'ORDER BY' with specific values For example, column 'TransType' consits of values 1, 2, 9, 4, 3. I wish to order them by 9, 3, 1, 4, 2.

SELECT Col1, Col2 FROM MyTable
ORDER BY (CASE WHEN TransType = 9 THEN 0
	       WHEN TransType = 3 THEN 1
	       WHEN TransType = 1 THEN 2
	       WHEN TransType = 4 THEN 3
	       WHEN TransType = 2 THEN 4 END)

Example 2: In this case, the table and data are MySQL. This is merely an example table with sample data to show what I'm looking for. So, in this table, I have many pet species, and I need a list of all pets in the following order: dogs, cats, snakes, and birds. I can't get the results in this order using the MySQL ORDER BY clause. I can acquire the results in the order I require by utilising the MySQL ORDER BY clause's FIELD() function. I can use the following query.

SELECT name, species FROM `pet`
ORDER BY FIELD(species, 'dog','cat','snake','bird'), name ASC

This query would return results sorted by name and ordered by species in the order I require.

Output:

name		species
Bowser		dog
Buffy		dog
Fang		dog
Claws		cat
Fluffy		cat
Slim		snake
Chirpy		bird
Whistler	bird

Example 3: With an example and sample code, ORDER BY specified value first in SQL. We've used stock name, price, and type in this instance. ORDER BY based on column values is used.

CREATE TEMPORARY TABLE tbl_Stock (
  Id  int ,
  Name varchar(99),
  Type varchar(25),
  Price NUMERIC(8,2)
 );

INSERT INTO tbl_Stock  (Id   ,Name  , Type ,Price)
                VALUES (1    ,'TCS' ,'C1'  ,2222.25);
INSERT INTO tbl_Stock  (Id   ,Name  , Type ,Price)
                VALUES (2    ,'SBIN' ,'B1'  ,400.50);
INSERT INTO tbl_Stock  (Id   ,Name  , Type ,Price)
                VALUES (3    ,'INFY' ,'B1'  ,1350.50);
INSERT INTO tbl_Stock  (Id   ,Name  , Type ,Price)
                VALUES (4    ,'RELIANCE' ,'A1'  ,1950.50);
INSERT INTO tbl_Stock  (Id   ,Name  , Type ,Price)
                VALUES (5    ,'RELAXO' ,'A1'  ,1190.50);

SELECT * FROM tbl_Stock ts  ORDER BY  CASE Type   WHEN 'C1' THEN 1
                                                  WHEN 'A1' THEN 2
                                                  ELSE 3
                                                  END ASC;

Example 4: A "name" field in the fruit table has the following unique values: Apple, Banana, Orange, and Pear. Each of these distinct values has several variations.

Let's pretend for the purpose of argument that we wish to sort the data by Banana, Apple, Pear, Orange, and then by their variants. Because an ascending or descending sort on this field would not function, you can't perform it using a conventional ORDER BY clause. Either a sort column or another solution would be required.

SELECT * FROM fruit 
ORDER BY FIELD(name, 'Banana', 'Apple', 'Pear', 'Orange'), variety;

Output:

+----------+--------+---------------------+
| fruit_id | name   | variety             |
+----------+--------+---------------------+
|       11 | Banana | Burro               |
|       12 | Banana | Cavendish           |
|       10 | Banana | Plantain            |
|        6 | Apple  | Cox's Orange Pippin |
|        7 | Apple  | Granny Smith        |
|        1 | Apple  | Red Delicious       |
|        8 | Pear   | Anjou               |
|        4 | Pear   | Bartlett            |
|        2 | Pear   | Comice              |
|        5 | Orange | Blood               |
|        3 | Orange | Navel               |
|        9 | Orange | Valencia            |
+----------+--------+---------------------+

The SELECT command in SQL returns rows in an infinite order when querying data from a table. We usually utilise the ORDER BY clause in the SELECT statement to sort the rows in the result set. This clause allows a SELECT statement to sort rows returned according to a sort expression in ascending or descending order.

Example 1: We have a table employee in the query below, and we will check for the ascending and descending length of the string in the name column after entering the ORDER BY clause using the SELECT statement. Let's look at the following query.

SELECT name from employee ORDER BY length(name) desc;

Example 2: order it by the those with the highest count first, add "ORDER BY COUNT(*) DESC" and to order it by the longest count first, add "ORDER BY LENGTH(field_to_query) DESC" e.g.:

SELECT LENGTH(field_to_query), COUNT(*) 
FROM table_to_query GROUP BY LENGTH(field_to_query) 
ORDER BY COUNT(*) DESC

SELECT LENGTH(long_description), COUNT(*) 
FROM table_to_query GROUP BY LENGTH(field_to_query) 
ORDER BY LENGTH(field_to_query) DESC

Example 3: query to sort by character length in descending order:

select *from orderingAADemo
order by length(Value) DESC, Value;

Output:

+-------+
| Value |
+-------+
| CCC   |
| AA    |
| A     |
| B     |
| C     |
+-------+

Example 4: You can use just simply LENGTH(), but beware, because it counts the byte number (which won't give you the expected result with multibyte strings).

SELECT * FROM table ORDER BY CHAR_LENGTH(field)

The ORDER BY phrase arranges the table in ascending or descending order. Use the ORDER BY operator to sort records using the aggregate function SUM(), which can compute the total number of columns.

Example 1: The GROUP BY clause arranges the result set by customer_id and adds up the amounts associated with the same customer. It adds the row to the returning result set if the customer_id updates.

To arrange the groups, use the ORDER BY clause with the GROUP BY clause in the following statement:

SELECT
	customer_id,
	SUM (amount)
FROM
	payment
GROUP BY
	customer_id
ORDER BY
	SUM (amount) DESC;

Example 2: The following example will utilise the SUM() function with the ORDER BY clause to add up each employee's income and then sort the result set in ascending order of total summed salary:

SELECT occupation, SUM(salary) AS "Total Salary"  
FROM employee_info  
GROUP BY occupation  
ORDER BY SUM(salary);  

Example 3: You must GROUP BY one of the non-grouped fields if you are combining grouped (i.e. SUM) and non-grouped fields.

SELECT SUM(something) AS fieldname
FROM tablename
ORDER BY fieldname

OR this:

SELECT Field1, SUM(something) AS Field2
FROM tablename
GROUP BY Field1
ORDER BY Field2

Example 4: calculates the total score for each player across all years.

SELECT login,
  SUM(score) AS total_score
FROM training
GROUP BY login
ORDER BY SUM(score) DESC;

Result:

login	total_score
Andy	70
Lucy	62
Gary	59

Discussion:

• If you wish to order rows based on a value provided by an aggregate function like SUM, use ORDER BY (). The aggregate function (in our case, SUM()) is called after the ORDER BY operator. To define a descending sort order, DESC is placed after this function. As a result, the largest aggregate numbers are displayed first, followed by gradually lower values. You can provide ASC or just omit either keyword to sort in ascending order, as ascending is the default sort order.
• We select each player's login and the total of their scores for all years in the query above. SUM() with the score column as an argument is used to calculate the overall score. This aggregate value has an alias (SUM(score) AS total score), which you can use in the ORDER BY clause instead of the aggregate function (ORDER BY total score DESC).
• It's worth noting that the GROUP BY includes login. If a column is used in SELECT, it must also be used in GROUP BY. Because we put this column in the SELECT, we utilise the GROUP BY clause followed by the field login in this example. In the query, you'll see that GROUP BY comes before ORDER BY.

MySQL has an operator called union. To filter the records, we can use ORDER BY. If you wish to select rows from numerous tables one after the other, or several sets of rows from a single table, use UNION.

In general, using order by clauses with union statements in SQL Server is not possible. To use the order by clause in SQL Server with a union statement, we must first create a sub query for union statements, and then we can use the order by clause.

Syntax:

Syntax to use ORDER BY clause along with UNION operator in a query:

SELECT column1, column2,……column
FROM table_name
WHERE condition

UNION

SELECT column1, column2,……column
FROM table_name
WHERE condition
ORDER BY column_name;

Example 1: In the above SQL query, the ORDER BY clause will be applied to the complete result set:

Select id,name,age
From Student
Where age < 15
Union
Select id,name,age
From Student
Where Name like "%a%"
Order by name

Example 2: Following is the sample sql server query to use order by clause with union statements:

SELECT * FROM
(
Select Id as UserId, UserName as Name, RegisterDate From UserDetails
Union
select UserId, EmpName as Name, ModifyDate as RegisterDate From UserDetails
) smptbl
ORDER BY RegisterDate DESC

As you can see in the following query, we're using a sub query to gather data from union statements and then using an order by statement sub query to display the data in descending order.

Example 3: query to apply UNION with Order by:

select id from UnionDemo1
 union
 select id from UnionDemo2
 order by id desc;

Output:

+------+
| id   |
+------+
|   10 |
|    9 |
|    8 |
|    7 |
|    6 |
|    5 |
|    4 |
|    3 |
|    2 |
|    1 |
+------+

SQL WHERE and ORDER BY Clauses | UniversalClass When you run a SELECT query with no sorting options, the SQL server provides the results in random order. The SQL server, in most situations, returns records in the same order as they were added to the database.

The order by and where with a condition to demonstrate a range The WHERE clause can be used with or without the ORDER BY declaration.

We've already used the WHERE clause to show you how to filter records using SELECT, UPDATE, and DELETE statements. Filter records using sub-SELECT statements, finite values, or comparison values. When filtering data, the WHERE clause gives you various alternatives.

Example 1: The LoginID column from the HumanResources table is selected in the example below. Employee table, VacationHours column equals 8, and data is ordered in ascending order by HireDate, which is inferred.

USE AdventureWorks;
GO 
SELECT LoginID
FROM HumanResources.Employee
WHERE VacationHours = 8
ORDER BY HireDate;
GO

Example 2: The equal (=) sign has been used in various contexts. You can also use analogies. For example, you might require a list of customers with IDs ranging from 300 to 400. These values are displayed in the SQL statement below.

SELECT * FROM Customer
WHERE CustomerId >=200 AND CustomerId <= 300
ORDER BY State 

Notice how the terms >= and <= are utilized. The values to the right of the equal sign are included. In other words, the search includes the numbers 200 and 300. Because there are no 200 or 300 in this Customer table, such numbers are not returned. The SQL statement also includes a "AND" in the syntax. In this scenario, the AND keyword incorporates a filter from the next SQL line, "Customer = 300." The AND keyword instructs the SQL statement to filter records based on both inputs.

Example 3: sql orderby with where:

select *
from table
where name like '%ci%'
order by case when name like 'ci%' then 0 else 1 end, name;

When you want to arrange the rows by month number, use the month names instead of the month numbers (you want January to be shown first, December last).

Example 1: To sort the rows by birthday_month:

There are two columns in the birthday table: name and birthday_month. The months are listed by name rather than by number.

name		birthday_month
Ronan Tisha	NULL
December	2020
Angie Julia	April
Narelle Dillan	April
Purdie Casey	January
Donna Nell	NULL
Blaze Graeme	October

Solution:

SELECT *
FROM birthday
ORDER BY STR_TO_DATE(CONCAT('0001 ', birthday_month, ' 01'), '%Y %M %d');

Output:

name		birthday_month
Donna Nell	NULL
Purdie Casey	January
Angie Julia	April
Narelle Dillan	April
Blaze Graeme	October
Ronan Tisha	NULL
December	October

Example 2: Display order by for year and month:

select billdate from test ORDER BY YEAR(billdate),Month(billdate)

Output:

2015_Jan
2015_Feb
2015_Mar
2015_Apr
2015_May
2015_Jun
2015_Jul
2015_Aug
2015_Sep
2015_Oct
2015_Nov
2015_Dec
2016_Jan