SQL order by
In this tutorial how to use the SQL order by clause with syntax and examples.
SQL ORDER BY clause is used to sort the data or record using ASC | DESC.
SQL ORDER BY sorts the records in ASC (ascending order) by default.
syntax 1:
SELECT column 1,column 2, .... column N
FROM table_name
ORDER BY column ...[ASC|DESC];
syntax 2:
SELECT column 1,column 2, .... column N
FROM table_name
ORDER BY column ASC,column DESC;
Here, we are created one table and named student_marks, and used order by to sort with marks and display it in the table.
create table student_marks
(
s_id int primary key,
s_name varchar(20) not null,
maths int ,
english int,
physics int ,
chemistry int,
Tamil int,
social int
);
Insert the records in the table using the INSERT INTO keyword.
INSERT INTO:
insert into student_marks values(101, 'Jack',25,52,35,85,45,55);
insert into student_marks values(102, 'Rithvik',55,62,65,85,45,55);
insert into student_marks values(103, 'Jaspreet',12,80,55,65,75,55);
insert into student_marks values(104, 'Praveen',100,85,85,55,95,78);
insert into student_marks values(105, 'Bisa',85,72,65,75,45,55);
insert into student_marks values(106, 'Suraj',02,62,35,55,35,35);
insert into student_marks values(107, 'abinaya',58,92,45,65,75,88);
insert into student_marks values(108, 'swetha',100,98,80,88,98,99);
insert into student_marks values(109, 'raju',25,56,85,85,45,55);
insert into student_marks values(110, 'rahul',66,72,35,95,45,55);
Let's check if the data is inserted into the student_marks table.
select * from student_marks;
| s_id | s_name | maths | English | Physics | chemistry | Tamil | social |
|---|---|---|---|---|---|---|---|
| 101 | Jack | 25 | 52 | 35 | 85 | 45 | 55 |
| 102 | Rithvik | 55 | 62 | 65 | 85 | 45 | 55 |
| 103 | Jaspreet | 12 | 80 | 55 | 65 | 75 | 55 |
| 104 | Praveen | 100 | 85 | 85 | 55 | 95 | 78 |
| 105 | Bisa | 85 | 72 | 65 | 75 | 45 | 55 |
| 106 | suraj | 2 | 62 | 35 | 55 | 35 | 35 |
| 107 | abinaya | 58 | 92 | 45 | 65 | 75 | 88 |
| 108 | swetha | 100 | 98 | 80 | 88 | 98 | 99 |
| 109 | raju | 25 | 56 | 85 | 85 | 45 | 55 |
| 110 | raghul | 66 | 72 | 35 | 95 | 45 | 55 |
ORDER BY DESC:
SELECT s_name,maths
FROM student_marks
ORDER BY maths DESC;
| s.name | maths |
|---|---|
| Praveen | 100 |
| swetha | 100 |
| Bisa | 85 |
| rahul | 66 |
| abinaya | 58 |
| Rithvik | 55 |
| Jack | 25 |
| raju | 25 |
| Jaspreet | 12 |
| Suraj | 2 |
ORDER BY ASC:
select s_name,maths
FROM student_marks
ORDER BY maths;
| s.name | maths |
|---|---|
| Suraj | 2 |
| Jaspreet | 12 |
| Jack | 25 |
| raju | 25 |
| Rithvik | 55 |
| abinaya | 58 |
| rahul | 85 |
| Bisa | 85 |
| swetha | 100 |
| Praveen | 100 |
SQL ORDER BY QUERY:
SELECT s_name ,score,city,status
FROM student
WHERE city in('chennai','vellore')
ORDER BY city DESC;
| s_name | score | city | status |
|---|---|---|---|
| Jaspreet | 342 | vellore | fail |
| Suraj | 224 | vellore | fail |
| raju | 351 | vellore | fail |
| rahul | 368 | chennai | pass |
| abinaya | 423 | chennai | pass |
| swetha | 563 | chennai | pass |
| Praveen | 498 | chennai | pass |
| jack | 297 | chennai | fail |
| Rithvik | 367 | chennai | pass |
SQL ORDER BY QUERY (WHERE, NOT, IN):
SELECT s_name,score,city,status
FROM student
WHERE NOT city IN ('chennai','vellore')
ORDER BY city DESC;
| s_name | score | city | status |
|---|---|---|---|
| Bisa | 397 | thiruvannamalai | pass |