The Sql Order by Clause
- Order by Clause is an optional expression in an sql query. If we don’t write, the query sorts according to the primary key column.
- Orderby clause is used to sort table according to the specified column or the columns.
- For ascending sort, we write “asc” or nothing after column name.
- For Descending sort, we write “desc” after column name.
- For Random sort, we write newid() function after order by clause
- Instead of column names, sequence number of column names would be wriiten.
Sql Order By Syntax
1 |
Select columnNames from tableName order by columNames |
1 |
Select columnNames from TableName where conditions order by columnNames |
TO DOWNLOAD THE SAMPLE LİBRARY DATABASE CLICK
Sql Order By Examples on Library Database
Example 1: List all students sorted by name
1 |
Select * from students order by name |
Result Of The Query;
Example 2: List all students sorted by surnames(rewerse), then by names
1 |
Select * from students order by surname desc,name |
or
1 |
Select * from students order by surname desc,name asc |
Result Of The Query;
Example 3: List the studets where student id in between 30 and 40 sorted by name
1 |
Select * from students where studentId between 30 and 40 order by surname desc,name asc |
Result Of The Query;
11 rows listed
Example 4: List all students randomly.
1 |
Select * from students order by newid() |
Result Of The Query;
505 rows listed
Example 5: List one student randomly selected.
1 |
Select top 1 * from students order by newid() |
Result Of The Query;
1 row listed.
Example 6: List randomly selected two students from 11B Class
1 |
Select top 2 * from students where class='11B' order by newid() |
Result Of The Query;
2 rows listed.
Example 7: List oldest student
1 |
Select top 1 * from students order by birthdate |
Result Of The Query;
Example 8: List youngest student
1 |
Select top 1 * from students order by birthdate desc |
Result Of The Query;
Example 9: List the book which have biggest page count
1 |
Select top 1 * from books order by pageCount desc |
Result Of The Query;
1 row listed.