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,
CASE
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,
CASE
<cfloop index="i" from="1" to="#ListLen(adminsort)#" step="1">
<cfset AdminID = ListGetAt(adminsort,i)>
WHEN admin_id = #Val(AdminID)# THEN #i#
</cfloop>
<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.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,
CASE
<cfloop index="i" from="1" to="#ListLen(adminsort)#" step="1">
<cfset AdminID = ListGetAt(adminsort,i)>
WHEN admin_id = #Val(AdminID)# THEN #i#
</cfloop>
<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.
In my next DataMgr presentation, I plan to show how DataMgr can simplify that task.
SELECT admin_id,FirstName,LastName
FROM admins
ORDER BY CASE
WHEN admin_id = #Val(Client.AdminID)# THEN 1
ELSE 0
END,
LastName