Problem with Sorting...

Posted on 2011-10-06
Last Modified: 2012-05-12

i have following values in day column.

Each value in above column denotes F - Friday,  M-Monday, W-Wednesday, Th-Thursday, Sa-Saturday, Su-Sunday, Tu-Tuesday.

I want to sort those values by day... i.e)  M,Tu,W,Th,F.Sa,Su

Anyone please assist with me for this requirement...
Question by:gpmsqldev
    LVL 32

    Accepted Solution

    There are a few ways to handle this ... personally, I would have used an integer in the Day column.

    But, given the data you have, I would add a DayLookup table with
    Code varchar(2) -- put the codes you show me in here
    Name varchar(8) -- put the full name here ... can be useful for displays in other cases
    Sequence TinyInt -- put the 0-based sequence numbers here.  In your case, "M" matches up with 0.

    Then to sort ...

    Select Field1, ... Fieldn, Day
    From MyTable Inner Join DayLookup on MyTable.Day = DayLookup.Code
    order by DayLookup.Sequence
    LVL 75

    Expert Comment

    by:Anthony Perkins
    You could also use a Computed Column to "translate" the code to a more useful tinyint...

    Author Closing Comment

    Its helps me to solve my problem... but i am not using this method . Anyway we have to use M as 1, and Tu as 2, and so on....

    So i used CASE statement for this problem.. and finally sorted correctly..

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Suggested Solutions

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now