locked
order by issue RRS feed

  • Question

  • User1623409651 posted

    Hi all , 

    How i can order by the result set with time string field 

    EmployeeID TimeString
    23 09:00 AM - 12:00 PM
    25 02:30 PM - 05:30 PM
    24 09:00 AM - 12:00 PM
    09 02:30 PM - 05:30 PM
    10 09:00 AM - 12:00 PM

    order by  the time string mean 09:00 AM - 12:00 PM first then 02:30 PM - 05:30 PM records will be displayed. I tried below but no luck

    ORDER BY 
      CASE 
        WHEN TimeString ='09:00 AM - 12:00 PM' THEN 1
        WHEN TImeString ='02:00 PM - 05:00 PM' THEN 2   
      END  

    Thanks

    Monday, May 7, 2018 12:44 PM

Answers

  • User347430248 posted

    Hi Rameezwahid,

    I try to create table like yours.

    Query:

    select EmployeeID,TimeString   from emp_data order by convert(time,(SUBSTRING(TimeString,1,8)))

    Output:

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 8, 2018 2:07 AM

All replies

  • User726159118 posted

    Hi RameezWaheed,

    Refer below query it may help you.

    SELECT 
    -- select the column which you want to select
     FROM(SELECT   CASE 
        WHEN TimeString ='09:00 AM - 12:00 PM' THEN 1
        WHEN TImeString ='02:00 PM - 05:00 PM' THEN 2   
      END  As OrderNo,
      -- Rest of the query FROM and table with where condition
      )a order by a.OrderNo

    Mark As Answer, If it is helpful.

    Monday, May 7, 2018 12:57 PM
  • User753101303 posted

    Hi,

    The other string is "'02:30 PM - 05:30 PM'"

    Your fix should work if you'll ever have those values. IMO you should always work with native values to avoid this kind of problem ie if you had a TimeStart and TimeEnd TIME columns you could just use ORDER BY TimeStart,TimeEnd, do calculations on those values etc...

    Monday, May 7, 2018 12:58 PM
  • User475983607 posted

    This is a design bug.  Store the time values are two numeric columns rather than a string.  

    Monday, May 7, 2018 12:58 PM
  • User-1716253493 posted

    convert the first part as time then use it as order by

    order by convert(...)

    Monday, May 7, 2018 11:38 PM
  • User347430248 posted

    Hi Rameezwahid,

    I try to create table like yours.

    Query:

    select EmployeeID,TimeString   from emp_data order by convert(time,(SUBSTRING(TimeString,1,8)))

    Output:

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 8, 2018 2:07 AM
  • User1623409651 posted

    Thank you all for your kind suggestions.

    Tuesday, May 8, 2018 5:24 AM