asked on

Updating a Field for First Record in Group

I'm using SQL Server 2008 R2

This might be difficult for me to explain, so please bear with me. All examples are the result if a query "SELECT TOP 6 * FROM MyTable" is run.

I have a table such as this:

Job    Phase    Order    StartDate
1234   ABC        1
1234   ABC        3
1234   ABC        4
1234   DEF        2
1234   DEF        5
1234   GHI        6
5678   ABC        1

Open in new window

What I need is an UPDATE query that will assign today's date in the StartDate field, but only the first Phase (by Order) for a given job.

Once the UPDATE is completed, the example above would look like this (given Job '1234'):

Job    Phase    Order    StartDate
1234   ABC        1      2013-03-14
1234   ABC        3
1234   ABC        4
1234   DEF        2      2013-03-14
1234   DEF        5
1234   GHI        6      2013-03-14
5678   ABC        1

Open in new window

To throw one little monkey wrench into this, neither the Phase nor the Order is always in some definite order. This is, it might look like this:

Job    Phase    Order    StartDate
1234   ABC        1
1234   ABC        3
1234   DEF        2
5678   ABC        1
1234   ABC        4
1234   GHI        6
1234   DEF        5

Open in new window

In which case the result would look like this:

Job    Phase    Order    StartDate
1234   ABC        1      2013-03-14
1234   ABC        3
1234   DEF        2      2013-03-14
5678   ABC        1
1234   ABC        4
1234   GHI        6      2013-03-14
1234   DEF        5

Open in new window

I hope I explained it well enogh.

TIA

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rshq

Hi

Please use this

select Job , Phase , Order , StartDate
from MyTable
order by job,order

Clif

ASKER

ScottPletcher,

Your code sample did not limit the update to just jobs with a job field value of '1234'

And, to answer your question about why job 5678 does not have a start date set, it's because I wanted to show an example where only certain jobs are set at a time, which ties in to my comment that you did not limit. When I use your code that start date is set for jobs 1234 and 5678 which isn't right. I just want to set the start date for jobs 1234.

Clif

ASKER

Ok, I got it. All I had to do was add:

WHERE
    mt.Job = '1234'  -- (Yes, it's a string)

Open in new window

To the end.

Thanks. :)

Clif

ASKER

Works perfect (with the addition of a where clause)

Scott Pletcher

Sorry, I didn't take this:

>> only the first Phase (by Order) for a given job. <<

literally enough. I thought "for a given job" meant for every unique job, but you literally meant for ONE given job :-) .