PHP Tutorials
- What is MySqli
- mysql query
- mysql query example
- InnoDB
- mysql column Type
- CRUD Example
- Connection Using Function
- mysql keys
- SELECT
- WHERE
- UPDATE
- Count no of Rows
- ALIAS
- AND, AND & OR
- BETWEEN
- COMPARISON OPERATOR
- DELETE
- DELETE LIMIT
- DISTINCT
- EXISTS
- FROM
- GROUP BY
- HAVING
- IN
- INTERSECT
- IS NULL & IS NOT NULL
- LIKE
- NOT
- ORDER BY
- SELECT LIMIT
- SUBQUERY
- TRUNCATE
- UNION && UNION ALL
- Concat & Group_Concat
- mysql Function
- Mysql Insert Id
- MySql Aggregate Function
- Mysql Join
- JOIN in MySql
- Trigger
- Procedure
- Transaction
- views
- Index
- SQL Injection
- Normalization
- Query Bind
- Interview Questions
Important Link
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 ];
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;