This looks strikingly similar to the one I posted...

Boris Pazin (10/11/2012)

---

No, I really appreciate because you spent your time to think about my problem. Thank you.

Sean Lange (10/11/2012)

---

Are you...

Boris Pazin (10/11/2012)

---

Sean Lange (10/11/2012)

In your case you are processing 200 rows and it takes 2 hours. That screams of a serious need of performance tuning. I bet you can...

Boris Pazin (10/11/2012)

---

Sean Lange (10/11/2012)

---

Boris Pazin (10/11/2012)

---

Sean Lange (10/11/2012)

---

If the job is taking 2 hours maybe you need to look at making the job run faster? That seems like a...

You should check out RedGate's SQL Search. It is a free plugin for SSMS and it is very solid tool for this type of thing. http://www.red-gate.com/products/sql-development/sql-search/%5B/url%5D

Boris Pazin (10/11/2012)

---

Sean Lange (10/11/2012)

---

If the job is taking 2 hours maybe you need to look at making the job run faster? That seems like a long time for a...

Because now we get increased index fragmentation, page splits and a slower process?

How is the performance from the select? It seems like you can't have that many rows. I am assuming the SegmentNo is a unique value? With a small int you...

If the job is taking 2 hours maybe you need to look at making the job run faster? That seems like a long time for a job to me.

Lokesh Vij (10/10/2012)

---

This query would give the best performance from all the queries posted above 🙂

SELECT dtStartDate,

(SELECT Max(dtRunDate)

...

You are quite welcome. Thanks for letting me know that worked for you.

As Michael said you need the TOP when using a derived table and an ORDER BY.

What I don't understand is why there is a need to have an order by...

yogi123 (10/10/2012)

---

Sean Lange (10/10/2012)

---

This looks a lot like homework. We don't typically provide answers for homework because we want you to learn.

However, it seems you want the most recent date...

This looks a lot like homework. We don't typically provide answers for homework because we want you to learn.

However, it seems you want the most recent date prior to the...

Here is an example of one way of deleting duplicates. There are plenty of other ways to do this.

create table #Dupes

(

IdentityValue int identity not null,

UserValue varchar(20),

AnotherValue varchar(10)

)

insert #Dupes

select 'asdf', 'Another'...