ORDER
BY clause is used to get the records from database in sorted way.
Syntax
SELECT
column_list
FROM
table_name
[WHERE
condition]
[ORDER
BY col1, col2…colN] [ASC/DESC]
ASC:
Gives the results in ascending order. By default data is sorted in ascending
order.
DESC:
Gives the results in descending order.
Execute
following statements in sql client.
CREATE
TABLE employee(
id int PRIMARY KEY,
name VARCHAR(25),
age int,
salary DECIMAL(10, 4),
mailId VARCHAR(25),
city VARCHAR(10)
);
INSERT
INTO employee VALUES (1, "Hari", 28, 12345.67,
"hari@hari.com", "Bangalore");
INSERT
INTO employee VALUES (2, "Sandesh", 31, 98345.00,
"sandesh@sandesh.com", "Trivendram");
INSERT
INTO employee VALUES (3, "Phalgum", 33, 119345.67,
"phalgun@hari.com", "Hyderabad");
INSERT
INTO employee VALUES (4, "Manju", 36, 87666.87,
"manju@sandesh.com", "Bangalore");
INSERT
INTO employee VALUES (5, "Rakesh", 26, 38000,
"rakesh@hari.com", "Bangalore");
INSERT
INTO employee VALUES (6, "Sankalp", 38, 87645.67,
"sankalp@sankalp.com", "Chenai");
INSERT
INTO employee VALUES (7, "Vadiraj", 40, 12345.67,
"vadi@hari.com", "Bangalore");
INSERT
INTO employee VALUES (8, "Prasob", 37, 12345.67,
"prasob@sandesh.com", "Trivendram");
INSERT
INTO employee (id, name, salary) VALUES (9, "Kesav", 123457.89);
INSERT
INTO employee VALUES (10, "Hari", 25, 198345.00,
"hari@krishna.com", "Trivendram");
INSERT
INTO employee VALUES (11, "Phalgun", 23, 119345.67,
"phalgun@p.com", "Hyderabad");
mysql> SELECT * FROM employee; +----+---------+------+-------------+---------------------+------------+ | id | name | age | salary | mailId | city | +----+---------+------+-------------+---------------------+------------+ | 1 | Hari | 28 | 12345.6700 | hari@hari.com | Bangalore | | 2 | Sandesh | 31 | 98345.0000 | sandesh@sandesh.com | Trivendram | | 3 | Phalgum | 33 | 119345.6700 | phalgun@hari.com | Hyderabad | | 4 | Manju | 36 | 87666.8700 | manju@sandesh.com | Bangalore | | 5 | Rakesh | 26 | 38000.0000 | rakesh@hari.com | Bangalore | | 6 | Sankalp | 38 | 87645.6700 | sankalp@sankalp.com | Chenai | | 7 | Vadiraj | 40 | 12345.6700 | vadi@hari.com | Bangalore | | 8 | Prasob | 37 | 12345.6700 | prasob@sandesh.com | Trivendram | | 9 | Kesav | NULL | 123457.8900 | NULL | NULL | | 10 | Hari | 25 | 198345.0000 | hari@krishna.com | Trivendram | | 11 | Phalgun | 23 | 119345.6700 | phalgun@p.com | Hyderabad | +----+---------+------+-------------+---------------------+------------+ 11 rows in set (0.00 sec)
a. Sort all employees
by ascending order of their name
mysql> SELECT * FROM employee ORDER BY name; +----+---------+------+-------------+---------------------+------------+ | id | name | age | salary | mailId | city | +----+---------+------+-------------+---------------------+------------+ | 1 | Hari | 28 | 12345.6700 | hari@hari.com | Bangalore | | 10 | Hari | 25 | 198345.0000 | hari@krishna.com | Trivendram | | 9 | Kesav | NULL | 123457.8900 | NULL | NULL | | 4 | Manju | 36 | 87666.8700 | manju@sandesh.com | Bangalore | | 3 | Phalgum | 33 | 119345.6700 | phalgun@hari.com | Hyderabad | | 11 | Phalgun | 23 | 119345.6700 | phalgun@p.com | Hyderabad | | 8 | Prasob | 37 | 12345.6700 | prasob@sandesh.com | Trivendram | | 5 | Rakesh | 26 | 38000.0000 | rakesh@hari.com | Bangalore | | 2 | Sandesh | 31 | 98345.0000 | sandesh@sandesh.com | Trivendram | | 6 | Sankalp | 38 | 87645.6700 | sankalp@sankalp.com | Chenai | | 7 | Vadiraj | 40 | 12345.6700 | vadi@hari.com | Bangalore | +----+---------+------+-------------+---------------------+------------+ 11 rows in set (0.00 sec)
b. Sort all employees
by ascending order of their name and age
mysql> SELECT * FROM employee ORDER BY name, age; +----+---------+------+-------------+---------------------+------------+ | id | name | age | salary | mailId | city | +----+---------+------+-------------+---------------------+------------+ | 10 | Hari | 25 | 198345.0000 | hari@krishna.com | Trivendram | | 1 | Hari | 28 | 12345.6700 | hari@hari.com | Bangalore | | 9 | Kesav | NULL | 123457.8900 | NULL | NULL | | 4 | Manju | 36 | 87666.8700 | manju@sandesh.com | Bangalore | | 3 | Phalgum | 33 | 119345.6700 | phalgun@hari.com | Hyderabad | | 11 | Phalgun | 23 | 119345.6700 | phalgun@p.com | Hyderabad | | 8 | Prasob | 37 | 12345.6700 | prasob@sandesh.com | Trivendram | | 5 | Rakesh | 26 | 38000.0000 | rakesh@hari.com | Bangalore | | 2 | Sandesh | 31 | 98345.0000 | sandesh@sandesh.com | Trivendram | | 6 | Sankalp | 38 | 87645.6700 | sankalp@sankalp.com | Chenai | | 7 | Vadiraj | 40 | 12345.6700 | vadi@hari.com | Bangalore | +----+---------+------+-------------+---------------------+------------+ 11 rows in set (0.00 sec)
c. Get employee who is
getting highest salary
mysql> SELECT * FROM employee ORDER BY salary DESC LIMIT 1; +----+------+------+-------------+------------------+------------+ | id | name | age | salary | mailId | city | +----+------+------+-------------+------------------+------------+ | 10 | Hari | 25 | 198345.0000 | hari@krishna.com | Trivendram | +----+------+------+-------------+------------------+------------+ 1 row in set (0.00 sec)
d. Get employee who is
staying in Bangalore and getting more salary.
mysql> SELECT * FROM employee WHERE city="Bangalore" ORDER BY salary DESC LIMIT 1; +----+-------+------+------------+-------------------+-----------+ | id | name | age | salary | mailId | city | +----+-------+------+------------+-------------------+-----------+ | 4 | Manju | 36 | 87666.8700 | manju@sandesh.com | Bangalore | +----+-------+------+------------+-------------------+-----------+ 1 row in set (0.00 sec)
e. Get young employee
who is getting more salary
mysql> SELECT * FROM employee ORDER BY age ASC, salary DESC limit 1; +----+-------+------+-------------+--------+------+ | id | name | age | salary | mailId | city | +----+-------+------+-------------+--------+------+ | 9 | Kesav | NULL | 123457.8900 | NULL | NULL | +----+-------+------+-------------+--------+------+ 1 row in set (0.00 sec)
mysql> SELECT * FROM employee ORDER BY age ASC, salary DESC limit 2; +----+---------+------+-------------+---------------+-----------+ | id | name | age | salary | mailId | city | +----+---------+------+-------------+---------------+-----------+ | 9 | Kesav | NULL | 123457.8900 | NULL | NULL | | 11 | Phalgun | 23 | 119345.6700 | phalgun@p.com | Hyderabad | +----+---------+------+-------------+---------------+-----------+ 2 rows in set (0.00 sec)
NULL
is the least value.
No comments:
Post a Comment