Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Sep 13, 1998

Introduction to Databases for the Web: Pt. 2 - Page 5

By Selena Sol

Order By

So far we have focussed on simply grabbing data from our database. We have not spent much time talking about how we can massage that data. One of the most common ways to manipulate data grabbed from a database is to order it, perhaps alphabetically by last name, or perhaps numerically, from high to low.

SQL provides the ORDER BY operator for just this purpose. The generic use of ORDER BY looks something like:

SELECT column_names 
FROM table
WHERE where_clause [OPTIONAL]
ORDER BY column_name;

Let's look at this by example. Suppose we have the following table:

Click here to view table 1.

Thus, if you would like to get an alphabetized list of artists, you would use:

SELECT BAND_NAME, COST
FROM CD
ORDER BY BAND_NAME;

You should get the following:

BAND_NAME              COST
---------------------------
Big Bad Voodoo Daddy  12.99
Indigo Swing          13.99
Levay Smith           13.99
Louis Jordan          12.99
Louis Jordan          10.99	
Louis Prima            9.99
---------------------------

Note that you can also specify the column number instead of the column name when defining an ORDER BY so that the following command would be just the same as the previous:

SELECT BAND_NAME, COST
FROM CD
ORDER BY 1;

Of course, you can also sort by multiple columns. That is, you specify an initial column to order by and then you choose subsequent columns with which to sort identical values in the initial column. For example, notice that if we sort by COST as well as BAND_NAME, the albums for Louis Jordan are sorted by cost as well as name:


SELECT BAND_NAME, COST
FROM CD
WHERE BAND_NAME LIKE 'Louis%'
ORDER BY BAND_NAME, COST;

You should get the following in which the 10.99 album is now listed first:

BAND_NAME      COST
-------------------
Louis Jordan  10.99
Louis Jordan  12.99	
Louis Prima    9.99
-------------------

Finally, you can use the DESC keyword to reverse the sort. Thus, to get a report sorted by cost in a descending order, you would use:

SELECT BAND_NAME, COST
FROM CD
ORDER BY COST DESC;

You would get the following view:

BAND_NAME               COST
----------------------------
Levay Smith            13.99
Indigo Swing           13.99
Big Bad Voodoo Daddy   12.99
Louis Jordan           12.99
Louis Jordan           10.99
Louis Prima             9.99
----------------------------


SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM