Description
The SQL ORDER BY clause is used to sort the records in the result set for a SELECT statement.
Sorting Results in Ascending Order
select empid, first_name, salary from employee1
where salary>5000
order by first_name asc
=================================================================
Sorting Results in descending order
select empid, first_name, salary from employee1
where salary>5000
order by first_name desc
=================================================================
Using both ASC and DESC attributes
select * from employee1
where salary>5000
order by salary desc,
last_name desc
=================================================================
Order by with RANDOM()
desc:
resulting record to be ordered randomly
SELECT emp_id, salary FROM
(
SELECT emp_id, salary FROM employee ORDER BY dbms_random.value
)
WHERE rownum=1;
Select a random row with MySQL:
SELECT column FROM table ORDER BY RAND () LIMIT 1;
Select a random row with Postgre SQL:
SELECT column FROM table ORDER BY RANDOM () LIMIT 1;
Select a random row with SQL Server:
SELECT TOP 1 column FROM table ORDER BY NEWID ();
Select a random row with oracle:
SELECT column FROM (SELECT column FROM table ORDER BY dbms_random.value) WHERE rownum = 1;
Select a random row with IBM DB2:
SELECT column RAND () as IDX FROM table ORDER BY IDX FETCH FIRST 1 ROWS ONLY;
=================================================================
ORDER BY LIMIT
We can retrieve limited rows from the database. I can be used in pagination where are forced to show only limited records like 10, 50, 100 etc.
Note: Below query will give you 21th to 40th rows.
SELECT name, age
FROM
(
SELECT name, age, ROWNUM r
FROM
(
SELECT name, age, FROM employee_data
ORDER BY age DESC
)
WHERE ROWNUM <=40
)
WHERE r >= 21;
