CODEWITHSUNDEEP
sqldatabasesqlite
MyNameIsGeorge
MyNameIsGeorge

Reputation: 1

Trying to understand simple SQL query with case statement

I am trying to understand this query:

SELECT * 
FROM servers 
ORDER BY 
    CASE 
        WHEN status = "ACTIVE" THEN 1 
        WHEN status = "INACTIVE" THEN 2 
        ELSE 3 
    END

I know this is selecting all rows from the server table and ordering them first with where column status = "ACTIVE" and then where status = "INACTIVE."

What is the syntax THEN 1...THEN 2 ELSE 3 END mean? I know END is to close the case statement, but what are 1, 2, and 3?

Upvotes: 0

Views: 32

Answers (1)

GregHNZ
GregHNZ

Reputation: 8979

Your CASE clause is in the ORDER BY section - it doesn't become part of the output, it's just used by the SQL engine for sorting.

The 1,2,3 are sortable values.

Basically it' saying to put the ACTIVE rows first (1), then the INACTIVE rows (2), then any rows that are neither (3) at the end.

Given that ACTIVE and INACTIVE sort the same way, I guess there are other values in the table that don't sort in that order (maybe CLOSED or DORMANT which would come before INACTIVE

Upvotes: 1

Related Questions