SQL to Put Record for Current User First

I was recently asked how to show a record for the current user before the record for other users - in one query. Here is my solution.

SELECT  admin_id,FirstName,LastName,
                         WHEN admin_id = #Val(Client.AdminID)# THEN '1'
                         ELSE '0'
                 END AS isCurrentUser
FROM            admins
ORDER BY        isCurrentUser DESC, LastName

I am assuming that the id for the current user is stored in "Client.AdminID". Hopefully it will be easy to modify this example to meet multiple needs.

You could use this technique to sort by a list as well.

If you have a list called "adminsort" that held the values of the admins in the order in which you wish to sort them, you could do this:

SELECT  admin_id,FirstName,LastName,
                        <cfloop index="i" from="1" to="#ListLen(adminsort)#" step="1">
                           <cfset AdminID = ListGetAt(adminsort,i)>
                           WHEN admin_id = #Val(AdminID)# THEN #i#
                        <cfset i = i + 1>
                        ELSE #i#
                  END AS SortVal
FROM                admins
ORDER BY        SortVal, LastName

I don't know if this would perform well, but it should work.

A better (and more common) technique if you want a sort order that will perform a little better (assuming the sort order will be more stable) would be to store the sort order in a numeric field in the database.

In my next DataMgr presentation, I plan to show how DataMgr can simplify that task.

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
Actually you can just move the case statement to the order by clause and it will have the same effect.

SELECT admin_id,FirstName,LastName
FROM admins
WHEN admin_id = #Val(Client.AdminID)# THEN 1
# Posted By Qasim Rasheed | 5/10/06 1:42 PM
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.