How do I sort my sqldatasource records in a particular way

I have my SqlDataSource control. It basically has UpdateCommand and SelectCommand. My gridview is bound to this sqldatasource control. My SelectCommand is:

Select Username, Status from Users Order by LastLoginTime desc

I have my table something like this:

ABC IN PROGESS  somedate
DEF COMPLETE    somedate
PQR PENDING     somedate
JLM COMPLETE    somedate
LKM IN PROGESS  somedate

Currently query results are sorted by lastlogintime (i.e somedate). But I want my results to be so开发者_StackOverflowrted by 2nd column(say this column name is Status). I want the records to be sorted in such a way that 1st all users whole status is IN PROGRESS should appear then PENDING and then COMPLETE. As you can say this is not a simple order by clause.

How can I sort in this way with my SqlDataSource control?

You should use Custom Sort Order using CASE in SQL. Try this

SELECT Name, Status FROM UsersOrder ORDER BY CASE Status
    WHEN 'IN PROGESS' THEN 1
    WHEN 'PENDING' THEN 2
    WHEN 'COMPLETE' THEN 3
    ELSE 99
END

So in effect your SelectCommand will be a hideous one-liner. :)

SelectCommand="SELECT Name, Status FROM UsersOrder ORDER BY CASE Status WHEN 'IN PROGESS' THEN 1 WHEN 'PENDING' THEN 2 WHEN 'COMPLETE' THEN 3 ELSE 99 END" 

I am afraid this is the only solution.