Assign case to user with lowest count

I am trying to do the following in my query however i only have the first part and im very new at SQL so i honestly have no idea how to get it right.

The objective is:

  1. Find the user with the lowest case count (which i have done) as below:

Select Top 1 assigned_to_name, count(*) as Cases from CSCASES Group by assigned_to_name order by 2

This returns my results as follows:
Sue 1

  1. Second part of my query is to assign the next case that is created to the user with the lowest count which in this case is Sue as above.

Is this possible with a function or is it easier to do it using a Trigger or SP?

Any help would be greatly appreciated


Please note the following

We need Sample Data to work with
to write the SQL

We need

  1. Create Table Script
  2. Insert into Table data Script


A trigger or SP would be used to do a task like that.