CODEWITHSUNDEEP
sqlsql-server-2012
interkey
interkey

Reputation: 355

SQL - Order by time, then text on beginning

I have a query below :

SELECT DISTINCT TimeSched from tbl_schedule

It returns this result:

TimeSched

Rest Day
11:00 AM - 08:00 PM
No Schedule
09:00 AM - 06:00 PM
10:00 AM - 07:00 PM

When I use ORDER BY TimeSched, it returns this result:

TimeSched

09:00 AM - 06:00 PM
10:00 AM - 07:00 PM
11:00 AM - 08:00 PM
No Schedule
Rest Day

However, my desired result is I want 'Rest Day' and 'No Schedule' on the first and second row by default, then followed by the order of schedules in ascending order. As seen below :

TimeSched

Rest Day
No Schedule
09:00 AM - 06:00 PM
10:00 AM - 07:00 PM
11:00 AM - 08:00 PM

Upvotes: 1

Views: 55

Answers (4)

Praveen ND
Praveen ND

Reputation: 560

Try to make use of the below code :

    DECLARE @TimeSched TABLE
    (ID INT IDENTITY(1,1),TimeSched VARCHAR(20))

    INSERT INTO @TimeSched
    VALUES
    ('Rest Day'),
    ('11:00 AM - 08:00 PM'),
    ('No Schedule'),
    ('09:00 AM - 06:00 PM'),
    ('10:00 AM - 07:00 PM')

    SELECT * FROM @TimeSched
    ORDER BY 
    CASE 
        WHEN TimeSched ='Rest Day' THEN 1
        WHEN TimeSched ='No Schedule' THEN 2
        ELSE 3 END

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270653

How about simply doing:

SELECT DISTINCT TimeSched 
FROM tbl_schedule
ORDER BY (CASE WHEN timesched LIKE '[a-zA-Z]%' THEN 1 ELSE 0 END), 
         timesched;

Upvotes: 1

interkey
interkey

Reputation: 355

Here is the answer to my question.

SELECT CASE TimeSched WHEN 'Restday' THEN 1 WHEN 'No Schedule' THEN 2 ELSE 3 END 
AS TimeSked, TimeSched ORDER BY TimeSked, TimeSched

Upvotes: 0

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

try this.

select * from timetable
order by 
iif(timesched in ('No Schedule','Rest Day'),'01' + timesched,timesched)

Append a prefix for No Schedule and Restday with 01 when ordering.. so 01No will go 1, 01R will go next then your 09 to 24

Upvotes: 1

Related Questions