SQL Query Order By Help Needed 1

[BHaines]

Just when you think your app is perfect, the people you wrote it for add another little qualifier. At current, the SQL query below pulls the required info for all records in the database, and orders them by ResearchID:

SQLStmt ="SELECT R.CreatedBy, R.ResearchID, R.AccountName, R.CurrentAccountNo, R.Status, R.RequestDate, R.DateAssigned, R.LocID, C.NickName + ' ' + C.LastName AS CreatedBy2, CASE (A.NickName + ' ' + A.LastName) WHEN ' ' THEN 'Unassigned' ELSE (A.NickName + ' ' + A.LastName) END AS AssignedTo FROM rrmain R INNER JOIN Employees C ON R.CreatedBy = C.EmployeeID LEFT OUTER JOIN Employees A ON R.AssignedTo = A.EmployeeID ORDER BY R.ResearchID"

This works peachy, however, now they want any research assigned to them specifically to appear at the top of the list for that person. The AssignedTo field has the number of the employee that is assigned to in it, BUT, this field can be null and will be until someone assigns it (Thus the part in the SQL query which decides if it's Unassigned or if it displays the name of the assignee). I could have it group by Assignees I think by adding R.AssignedTo to the ORDER BY, but that will group them asc or desc, not place the logged in Assignee's stuff at the top.

I am so lost on how to remedy this, it isn't even funny. Any and all help is much appreciated.

 

[unseth]

hm.. not sure you can do an order by like that.

What you might have to do is two select statements. One to pull out the AssignedTo for the logged in user, then another that orders it for everyone else except the assignedTo person. The second one can be ordered whatever way you need.

 

[BHaines]

Thanks unseth, that worked fine. It was a lot more work on my side, but it works right. Thanks muchly!

 

[Mr3Putt]

Or, couldn't you use a CASE, like;

  SELECT fld1, fld2, fld3,
  CASE R.AssignedTo
    WHEN loggedInUser THEN 0
    WHEN NULL THEN 1
    ELSE 2
  END AS sortBy

then order by that column... Loggged-In-User comes first, then NULL, then everything else.

 

[BHaines]

Not sure about that. I'm not experienced with case statements at all so I wouldn't know enought to convert what you've posted to work with my variables and such. What I have at the moment is working, but I may look into case statements for my next project.