SQL Server: Using CASE statement in Order By clause

Hi,

I have come across a nice functionality to be used in SQL Server. Sometimes we need to order the records in some sets, like if there are 100 records, we may need to show 50-55 records IDs first, then 1-10 at the second and rest at last.

We might be using Unions for the same, but we can achinve this using CASE in Order By clause.

Example: I have a table Customer as (SELECT TOP 100 * FROM Customer)top 100 records

 

 

 

 Now if I use: (SELECT * FROM Sales.Customer ORDER BY CASE WHEN CustomerID >= 50 AND CustomerID <= 55 THEN  1 WHEN CustomerID >= 1 AND CustomerID <= 10 THEN  2 ELSE END)

Using CASE in Order By

 

Advertisements
This entry was posted in SQL Server and tagged . Bookmark the permalink.

10 Responses to SQL Server: Using CASE statement in Order By clause

  1. Sumeet says:

    nice one 🙂

  2. rajesh says:

    hi

    man

    i visited in your web bt dont find you there’
    i do not know much about that
    i m just sending you a message
    ok
    bye

  3. Sugavan says:

    Ofcourse…the END is always important 🙂

  4. Abbas says:

    I’ve same kind of prob.
    I need to sort the data so that if there is a NULL in Column3, it would come on the top, then the Column3 needs to be sorted in Descending order. I wrote the following query but it brings all the nulls at the bottom instead of top. Any help would be appretiated.

    select column1, column2, column3
    from Table1 order by
    case when column3 is Null then column3 end,
    case when column3 is not Null then column3 end desc, column2

  5. Abbas says:

    Well, never mind, I got the solution, we can use RANK() OVER…

  6. Hi Abbas

    Using the same CASE statement in the Order By clause, you can get the records on the top where column3 is null and rest in desc order as:

    Select column1, column2, column3
    from Table1 order by
    (case
    when column3 is Null then 1 ELSE 2
    END) asc,
    column3 desc

    : This will first arrange the NULL values on the top and rest on the second level, then will order the second level values in desc order.

  7. hope it worked for u.

    -Munish

  8. Abbas says:

    Perfect !! Thanks Munish.

  9. Mahes Khadse says:

    Hi Munish Bansal,

    Thanks for the query….

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s