troubleshooting Question

Updating a Field for First Record in Group

Avatar of Clif
ClifFlag for United States of America asked on
Microsoft SQL Server
6 Comments1 Solution408 ViewsLast Modified:
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
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros