Introduction
ORDER BY clause is used in a SELECT statement to sort results either in ascending or descending order. By default ORDER BY sorts the data in ascending order. Use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.
OFFSET excludes the first set of records. It can only be used with an ORDER BY clause.
Syntax
SELECT column_list FROM table_name WHERE condition ORDER BY column_names LIMIT M OFFSET N
table_name is the name of the table from which data is retrieved.column_list includes one or more columns from which data is retrieved. ORDER BY clause order the result based on column_names. LIMIT clause forces the result to have maximum of M rows. OFFSET clause exclude the first N rows from query result.
Example
Consider below table
ORDER BY Example
SELECT query result are ordered by column lastName in ascending order.
SELECT firstName, lastName FROM employeeinfo ORDER BY lastName /* Result of Query*/ firstName lastName Rahul Bhagwat Shekar Chandra Erica Edwards Stephen Fleming Priya Gowda Anjali Sharma Shekar Sharma Sebastian Smith
Order the result by last name in descending order
SELECT firstName, lastName FROM employeeinfo ORDER BY lastName DESC /* Result of Query */ firstName lastName Sebastian Smith Anjali Sharma Shekar Sharma Priya Gowda Stephen Fleming Erica Edwards Shekar Chandra Rahul Bhagwat
List all employee ordered by last name, then by city. Ordering by one or more columns is possible.
SELECT firstName, lastName,city FROM employeeinfo ORDER BY lastName, city /* Result of Query */ firstName lastName city Rahul Bhagwat Noida Shekar Chandra Delhi Erica Edwards Chennai Stephen Fleming Delhi Priya Gowda Pune Anjali Sharma Mumbai Shekar Sharma Patna Sebastian Smith Patna
OFFSET Examples
Skip the first row and show top three result.
SELECT firstName, lastName FROM employeeinfo ORDER BY lastName DESC LIMIT 3 OFFSET 1 /* Result of Query */ firstName lastName Anjali Sharma Shekar Sharma Priya Gowda