SQL query in SQL Server 2008 - grouping and sorting

Question

I'm looking for SQL query for a problem that I have.

I have 2 columns - the first is "employee full name" (containing FULL name) and the second is "Hours".

Employee Full Name | Hours | grading
-------------------------------------
john ran             122
john medi            177
john mat             138
jack rom             66
jack ton             88

I would like to update the database with the "grading" column in the following way:

grouping employees by their FIRST name, and then grading them (in ascending order) by working hours (highest working hours for employee with a specific first name) gets grade of 1, second 2, etc...).

The result should be like this:

Employee Full Name | Hours | grading
------------------------------------
john ran             122      3
john medi            177      1
john mat             138      2
jack rom             66       2
jack ton             88       1

There are no 2 employees with the same full name

It is best to describe your database structure in sqlfiddle.com. We can then more easily work out a solution for you. — ron tornambe
– ron tornambe, Commented Jan 31, 2013 at 21:52
Employee first ans last name are not in diffrent columns? That would make your life eazier. — Hiten004
– Hiten004, Commented Jan 31, 2013 at 21:55
Are you sure you want to be ranking employees by their first name? This seems extremely arbitrary. In your example, "jack ton" has a better ranking than "john mat" despite working more hours. Also, are you sure all names are "first last"? What about "first"? Or "first m last"? — Paul Williams
– Paul Williams, Commented Jan 31, 2013 at 22:04

Taryn · Accepted Answer · 2013-01-31 22:03:49Z

3

You can use a CTE to perform this update using row_number()

;with cte as
(
  select *,
    row_number() 
      over(partition by substring([EMPLOYEE FULL NAME], 1, charindex(' ', [EMPLOYEE FULL NAME])) 
           order by hours desc) rn
  from yourtable
)
update cte
set grading = rn;

See SQL Fiddle with Demo

Ideally, you will want to normalize your database to have the first and last name in separate columns.

answered Jan 31, 2013 at 22:03

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query in SQL Server 2008 - grouping and sorting

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related