Tags: MSSQL , MySQL

ORDER BY 
ORDER BY is used in database to sort data in Ascending or Descending order. ORDER BY keyword is available in all across different database platforms. It comes with two option ASC and DESC. If you don't apply order pattern(ASC or DESC) then it'll take ASC as default pattern.

Microsoft SQL Server and MySQL (Ascending) : SELECT * FROM user_master ORDER BY user_name
Microsoft SQL Server and MySQL (Descending) : SELECT * FROM user_master ORDER BY user_name DESC

I tested ORDER BY on two columns in Microsoft SQL Server and MySQL. You can share for other database platform.

Microsoft SQL Server Start up scripts

/* MSSQL tables and Insert queries */
/****** Object:  Table [dbo].[post]    Script Date: 05/22/2014 17:25:18 ******/
CREATE TABLE [dbo].[post](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [post] [varchar](max) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT post ON;
INSERT [dbo].[post] ([id], [post]) VALUES (1, N'Post 1')
INSERT [dbo].[post] ([id], [post]) VALUES (2, N'Post 2')
SET IDENTITY_INSERT post OFF;

/****** Object:  Table [dbo].[comments]    Script Date: 05/22/2014 17:25:18 ******/
CREATE TABLE [dbo].[comments](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [comment] [varchar](max) NULL,
 [postID] [int] NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT comments ON;
INSERT [dbo].[comments] ([id], [comment], [postID]) VALUES (1, N'comment 1 of 1', 1)
INSERT [dbo].[comments] ([id], [comment], [postID]) VALUES (2, N'comment 2 of 1', 1)
INSERT [dbo].[comments] ([id], [comment], [postID]) VALUES (3, N'comment 1 of 2', 2)
INSERT [dbo].[comments] ([id], [comment], [postID]) VALUES (4, N'comment 2 of 2', 2)
INSERT [dbo].[comments] ([id], [comment], [postID]) VALUES (5, N'comment 3 of 2', 2)
SET IDENTITY_INSERT comments OFF;

MySQL Start up scripts

/* MySQL tables and Insert queries */
/****** Object:  Table post    Script Date: 05/22/2014 17:25:18 ******/
CREATE TABLE `post` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT post (id, post) VALUES (1, 'Post 1');
INSERT post (id, post) VALUES (2, 'Post 2');

/****** Object:  Table comments    Script Date: 05/22/2014 17:25:18 ******/
CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `comment` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `postID` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT comments (id, comment, postID) VALUES (1, 'comment 1 of 1', 1);
INSERT comments (id, comment, postID) VALUES (2, 'comment 2 of 1', 1);
INSERT comments (id, comment, postID) VALUES (3, 'comment 1 of 2', 2);
INSERT comments (id, comment, postID) VALUES (4, 'comment 2 of 2', 2);
INSERT comments (id, comment, postID) VALUES (5, 'comment 3 of 2', 2);

ORDER BY on Single Column

SELECT *
FROM post p
LEFT JOIN comments c
ON p.id = c.postID
ORDER BY p.id DESC;

ORDER BY on Two Columns

SELECT *
FROM post p
LEFT JOIN comments c
ON p.id = c.postID
ORDER BY p.id DESC, c.id DESC;

• You can't apply ORDER BY on two columns within same table. If you apply ORDER BY on same table. It'll take last column name and apply ORDER BY pattern on it.

Tags: MSSQL , MySQL