CODEWITHSUNDEEP
sqlstored-procedures
Purplegoldfish
Purplegoldfish

Reputation: 5284

How to write this SQL Order By Clause

I have a SQL Query which I am trying to write and am now a bit stuck on how to write the order by clause.

Basically the table I am selecting from has items with a severity value. I want to select these items and order them so that the Severity column is ordered Severity 1-4 then 0 and the log date is descending for each.

Severity 1 is highest 4 is lowest and 0 respresents an unassigned severity, I need to display these items Highest severity, oldest item first, lowest severity, newest item last.

My query so far:

SELECT 
        [TicketID],
        [type],
        [Product],
        [Description],
        [LoggedBy],
        [LogDate],
        [Department],
        [AssignedTo],
        [Severity],
        [Status],
        [LastUpdatedBy],
        [LastUpdatedDate]
    FROM 
        SupportTicketsTbl 
    WHERE 
        TicketID NOT IN
        (
            SELECT 
                tck.ticketID  
            FROM 
                SupportTicketsTbl tck
            JOIN
                tblTicketsInEvents tie
            ON
                tck.TicketID = tie.ticketID 
            JOIN
                tblSupportEvent ev
            ON
                tie.eventID = ev.id 
            where 
                ev.type = 1
        )
    AND
        Status <> 'Complete'

I guess the easiest way is to create a table variable and select all the Items that are not 0 into it in the order I want, then select all the 0 items into my table variable, and finally just select everything back out of the table variable, but this seems a bit messy so im wondering if there is a more elegant solution?

Thanks

Upvotes: 1

Views: 464

Answers (4)

He Hui
He Hui

Reputation: 2236

Unless I'm very much mistaken, it's something like this:

ORDER BY severity DESC, date DESC

Insert that line into your SQL.

This will sort the data by Severity first, and if they have the same severity, then sort it according to date.

Upvotes: 0

Jamie F
Jamie F

Reputation: 23789

Since you didn't like the UNION answer, and I'm not sure if UNION is guaranteed to preserve order...

ORDER BY CASE WHEN severity = 0 THEN 999 ELSE Severity END, date

Upvotes: 2

a&#39;r
a&#39;r

Reputation: 36999

You can order by a case statement like this:

ORDER BY CASE Severity WHEN 0 THEN 1 ELSE 2 END, Severity

Upvotes: 1

Paula Bean
Paula Bean

Reputation: 458

First, Select all of the ones with severity levels 1-4 using a standard orderby clause, then union the results with a second query that selects only the ones with severity level 0.

Upvotes: 0

Related Questions