Sort By Column Value

We do sorting by column names almost everyday while writing database queries. Below is one such simple query

SELECT customerid, employeeid, orderdate  FROM dbo.orders  ORDER BY customerid

But, today I faced a situation where I had to write a query that returns all the rows however some of the rows appeared on the top in the list if they match a specified criteria. Lets take the example of above query where we want to get a list of all the customers but customers having customerid 'VINET' should appear on top. I came up with the below query:

DECLARE @customerid AS VARCHAR(100)  SET @customerid='VINET'  SELECT customerid, employeeid, orderdate,  (CASE @customerid WHEN '' THEN  null WHEN customerid THEN customerid END) AS 'sortColumn'  FROM orders  ORDER BY sortColumn DESC, employeeid

In the above query, 'sortColumn'  is a temporary column that is being used just to sort the whole result set by its value. 

This might not be a perfect solution as I am not a SQL expert but, I am content with it for the time being as long as it servers the purpose. If you think there is a much better way of doing the same thing, then you are more than welcome to share it here.


Post a Comment