what is order by?

MySQL ORDER BY clause is used to sort the records in your result set. it sort the data in ascending or descending order.

SELECT column_name FROM tables [WHERE conditions] ORDER BY column_name [ ASC | DESC ];

If the ASC or DESC modifier is not provided in the ORDER BY clause, the results will be sorted by column_name in ascending order. This is equivalent to ORDER BY expression ASC.

The ORDER BY clause can be used in a SELECT statement, SELECT LIMIT statement, and DELETE LIMIT statement.

SELECT * FROM itechxpert WHERE name = 'mahtab' ORDER BY first_name;

Sorting in descending order

SELECT last_name, first_name, city FROM contacts WHERE last_name = 'Johnson' ORDER BY city DESC;

Sorting without using ASC/DESC attribute

the sort order is defaulted to ASC or ascending order.

SELECT city FROM customers WHERE customer_name = 'Apple' ORDER BY city;
OR
SELECT city FROM customers WHERE customer_name = 'Apple' ORDER BY city ASC;

Sorting by relative position

SELECT last_name, first_name, city FROM contacts WHERE last_name = 'Johnson' ORDER BY 3 DESC;
OR
SELECT last_name, first_name, city FROM contacts WHERE last_name = 'Johnson' ORDER BY city DESC;

Using both ASC and DESC attributes

SELECT supplier_city, supplier_state FROM suppliers WHERE supplier_name = 'Microsoft' ORDER BY supplier_city DESC, supplier_state ASC;
SELECT first_name, address FROM itechxpert WHERE emp_id > 10 ORDER BY first_name DESC, address ASC;

Using Limit

SELECT contact_id, last_name, first_name FROM contacts WHERE website = 'TechOnTheNet.com' ORDER BY contact_id DESC LIMIT 5;

SELECT contact_id, last_name, first_name FROM contacts WHERE website = 'TechOnTheNet.com' ORDER BY contact_id ASC LIMIT 5;

How to short data using Two column?

sort the records according to salary in descending order first and then by last name in ascending order. After the ORDER BY keyword, add the name of the column by which you’d like to sort records first (salary). Then, after a comma, add the second column (last_name). You can modify the sorting order (ascending or descending) separately for each column. If you want to use ascending (low to high) order, you can use the ASC keyword;

SELECT id, first_name, last_name, salary FROM employee ORDER BY salary DESC, last_name;

Using ORDER BY with Multiple Columns

SELECT id, furniture, points FROM furniture _data ORDER BY point DESC , furniture;

Expressions in the ORDER BY

SELECT id, furniture, points, price FROM furniture _data ORDER BY price-120*points/100;

ORDER BY CASE

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

Order by Clause with Multiple Columns

It is also possible in MySQL to use Order by Clause with multiple columns.

SELECT Id, Department, Name, Salary, City, Age, Gender FROM Employee ORDER BY Department DESC, Name ASC;

Sorting Data in MySQL by the Relative Position

We can also use the ORDER BY clause to sort the data by relative position in the result set, where the first field in the result is set to 1. The next field is 2, and so on. In the below example, the result set contains four columns. So, the relative position will be 1 for Name, 2 for Department, 3 for Gender, and 4 for City.

SELECT Name, Department, Gender, City FROM Employee ORDER BY 2 ASC;

Previous Next

Review & Rating

0.0 / 5

0 Review

5

(0)

4

(0)

3

(0)

2

(0)

1

(0)