Sql ORDER BY

Sql ORDER BY
Nikhil
Published on 2021-08-14 13:29:37

Sorting by column number (instead of name)

You can use a column's number (where the leftmost column is '1') to indicate which column to base the sort on, instead of describing the column by its name.

Pro: If you think it's likely you might change column names later, doing so won't break this code.

Con: This will generally reduce the readability of the query (It's instantly clear what 'ORDER BY Reputation' means, while 'ORDER BY 14' requires some counting, probably with a finger on the screen.)

This query sorts the result by the info in relative column position 3 from select statement instead of column name Reputation.

SELECT DisplayName, JoinDate, Reputation FROM Users ORDER BY 3

DisplayName JoinDate Reputation
Community 2008-09-15 1
Jarrod Dixon 2008-10-03 11739
Geoff Dalgas 2008-10-03 12567
Joel Spolsky 2008-09-16 25784
Jeff Atwood 2008-09-16 37628

Use ORDER BY with TOP to return the top x rows based on a column's value

In this example, we can use GROUP BY not only determined the sort of the rows returned, but also what rows are returned, since we're using TOP to limit the result set.

Let's say we want to return the top 5 highest reputation users from an unnamed popular Q&A site. Without ORDER BY

This query returns the Top 5 rows ordered by the default, which in this case is "Id", the first column in the table (even though it's not a column shown in the results).

SELECT TOP 5 DisplayName, Reputation
FROM Users

returns...

DisplayName Reputation
Community 1
Geoff Dalgas 12567
Jarrod Dixon 11739
Jeff Atwood 37628
Joel Spolsky 25784

With ORDER BY

SELECT TOP 5 DisplayName, Reputation
FROM Users ORDER BY Reputation desc

returns...

DisplayName Reputation
JonSkeet 865023
Darin Dimitrov 661741
BalusC 650237
Hans Passant 625870
Marc Gravell 601636

Remarks

Some versions of SQL (such as MySQL) use a LIMIT clause at the end of a SELECT, instead of TOP at the beginning, for example:

SELECT DisplayName, Reputation
FROM Users
ORDER BY Reputation DESC
LIMIT 5

Customized sorting order

To sort this table Employee by department, you would use ORDER BY Department. However, if you want a different sort order that is not alphabetical, you have to map the Department values into different values that sort correctly; this can be done with a CASE expression:

Name Department
Hasan IT
Yusuf HR
Hillary HR
Joe IT
Merry HR
Ken Accountant

SELECT *
FROM Employee
ORDER BY CASE Department
WHEN 'HR' THEN 1
WHEN 'Accountant' THEN 2
ELSE 3
END;

Name Department
Yusuf HR
Hillary HR
Merry HR
Ken Accountant
Hasan IT
Joe IT

Order by Alias

Due to logical query processing order, alias can be used in order by.

SELECT DisplayName, JoinDate as jd, Reputation as rep
FROM Users
ORDER BY jd, rep

And can use relative order of the columns in the select statement .Consider the same example as above and instead of using alias use the relative order like for display name it is 1 , for Jd it is 2 and so on

SELECT DisplayName, JoinDate as jd, Reputation as rep
FROM Users
ORDER BY 2, 3

Sorting by multiple columns

SELECT DisplayName, JoinDate, Reputation FROM Users ORDER BY JoinDate, Reputation

DisplayName JoinDate Reputation
Community 2008-09-15 1
Jeff Atwood 2008-09-16 25784
Joel Spolsky 2008-09-16 37628
Jarrod Dixon 2008-10-03 11739
Geoff Dalgas 2008-10-03 12567

ATutorialHub Related Guide

Comments (8)

Leave a Comment

Your email address will not be published. Required fields are marked*

User Comments

html tutorial comments

panduranga gupta

2021-07-05 07:03:13

good website for learning and help me a lot

html tutorial comments

raju

2021-09-25 14:58:47

The awsome website i am looking like for a long time, good work atutorialhub team keep doing

html tutorial comments

Shivani

2021-09-01 15:03:56

Learning a lot from the courses present on atutorialhub. The courses are very well explained. Great experience

html tutorial comments

Harshitha

2021-09-10 15:05:45

It is very helpful to students and easy to learn the concepts

html tutorial comments

Sowmya

2021-09-14 15:06:41

Great job Tutorials are easy to understand Please make use of it

html tutorial comments

Zain Khan

2021-09-18 15:07:23

Great content and customized courses.

html tutorial comments

Rudrakshi Bhatt

2021-09-09 15:08:10

Well structured coursed and explained really well!

html tutorial comments

Pavana Somashekar

2021-09-11 15:09:08

Good platform for beginners and learn a lot on this website