DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. SQL Database Hacks Using AS and ORDER BY

SQL Database Hacks Using AS and ORDER BY

Here are a few hacks you can throw into your SQL to help sort and organize what you want to pull from your data while saving time.

Darren Perucci user avatar by
Darren Perucci
·
Brian Wheeler user avatar by
Brian Wheeler
·
Jun. 21, 16 · Tutorial
Like (2)
Save
Tweet
Share
2.54K Views

Join the DZone community and get the full member experience.

Join For Free

Image title

When retrieving data from an SQL database, it is often handy to be able to reference a column name with a shorter or more meaningful name when you make use of the results. Also, many times it is very helpful if the results are ordered in ascending or descending order by the content of a particular column. These things can be done in an SQL query using AS and ORDER BY.

How to Use AS

In SQL, the AS keyword can be used to make referencing a column easier. For example, you may be querying a table with one or more long column names, like the following table:

Table: orders

+—-+———————————- +———————————–+

| id | number_of_rocks_ordered | number_of_socks_ordered |

+—-+———————————–+———————————–+

| 1 | 2 | 1 |

| 2 | 3 | 3 |

+—-+———————————–+———————————–+

To make the column names easier to deal with in clauses, subqueries, or on the programming side, you can use AS in your query to provide a shorter alias to use later, as in the following example:

SELECT id, number_of_rocks_ordered AS rocks FROM orders;

Now, the column name can later be referred to as simply ‘rocks’ later.

The results will look like the following:

+—-+——–+

| id | rocks |

+—-+——–+

| 1 | 2 |

| 2 | 3 |

+—-+——-+

This can be especially helpful when providing an API for programmers, as the shorter alias can be returned from the API route to make for a little less typing on the programming side.

How to use ORDER BY

The ORDER BY command can be used to order the results of a query by a particular column. So, using the table from above, you could decide to order the results by the number or rocks ordered, from most to least. To do this, add ORDER BY, followed by the column name or alias, followed by ASC (for ascending) or DESC (for descending), as in the following example:

SELECT

id,

number_of_rocks_ordered

FROM

orders

ORDER BY

number_of_rocks_ordered DESC;


Since you wanted the number of rocks ordered from most to least, DESC was used. This produces the following set of results:

+—-+———————————–+

| id | number_of_rocks_ordered |

+—-+———————————–+

| 2 | 3 |

| 1 | 2 |

+—-+———————————–+

Combining AS and ORDER BY

Since you can use an alias created by the AS keyword in a later clause, you can combine AS and ORDER BY for this example to provide a result set that is both ordered by most rocks ordered to least, but also has the shorter column name of ‘rocks’. Here is the query to do this:

SELECT

id,

number_of_rocks_ordered AS rocks

FROM

orders

ORDER BY

rocks DESC;


Notice that the shorter 'rocks' alias is used in the ORDER BY clause, making that a little shorter. The result set from using both AS and ORDER BY looks like this:

+—-+——–+

| id | rocks |

+—-+——–+

| 2 | 3 |

| 1 | 2 |

+—-+——–+

Now the result set is both ordered and uses a shorter name for the number of rocks ordered!

Database sql

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Load Balancing Pattern
  • Stream Processing vs. Batch Processing: What to Know
  • Top 5 Java REST API Frameworks
  • Using JSON Web Encryption (JWE)

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: