Order By Query

Generally, MySQL returns the records in the same order of insertion. but sometimes it's required that result must be sorted in some meaningful full order or based on column value. to sort the result, MySQL provides ORDER BY.

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;

CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE );

INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1,"Macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (2,"Fuji",  'N', '2022-02-2');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (3,"Gala",  'Y', '2022-03-3');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (4,"Jonagold", 'N', '2022-04-4');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (5,"GrannySmith", 'Y', '2022-05-5');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (6,"PinkLady", 'N', '2022-06-6');

retrieve data sorted by a particular column value.

SELECT * FROM apple ORDER BY apple_name;

the default sorting order is ascending, ie smallest to largest value. the reverse order, add the DESC keyword

SELECT * FROM apple ORDER BY apple_name DESC;

records can be sorted on multiple columns, MySQL will start from the 1st column mentioned, if values are same then will sort on basis of 2nd column

SELECT * FROM apple ORDER BY apple_name, apple_id;

each column can have its own sorting directions, in not mentioned then ascending is considered as default sorting direction.

SELECT * FROM apple ORDER BY apple_name ASC, apple_id DESC;

By default, sorting is done in a case insensitive manner. case sensitive ordering can be done using the BINARY keyword

DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb; 
USE AppleDb;

CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE );

INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1,"Macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (2,"macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (3,"Macintosh", 'Y', '2022-01-1');

retrieve the records in case sensitive order, uppercase first then lowercase

SELECT * FROM apple ORDER BY BINARY apple_name;