Clif
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:
Once the UPDATE is completed, the example above would look like this (given Job '1234'):
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:
I hope I explained it well enogh.
TIA
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
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
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
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
I hope I explained it well enogh.
TIA
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Ok, I got it. All I had to do was add:
Thanks. :)
WHERE
mt.Job = '1234' -- (Yes, it's a string)
To the end.Thanks. :)
ASKER
Works perfect (with the addition of a where clause)
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 :-) .
>> 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 :-) .
Please use this
select Job , Phase , Order , StartDate
from MyTable
order by job,order