Why not clean the data and make sure it stays clean forever, then simply doing a single join?

You need to return the max row per id from table b in a derived table and then join that to table A.  I would give more details but it's...

It will work without the ", 'U'", since the name of the objects must be unique to a single owner, then there's no point in having to specify the object...

Option b is to insert the results of this query into a table variable or temp table, then do the self join there.

He y jeff,can you repost your list of valid points, I'd like to read them now that our homework is over!

Hence my more complicated / advanced t-sql solution .

Ya even better, use a single in statement.  Learn something new every day .

In a procedure :

CREATE TABLE #tmp (ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, DesignationName VARCHAR(100), Description VARCHAR(1000))

INSERT INTO #tmp (DesignationName, Description)

SELECT DesignationName, Description FROM dbo.YourBaseTable ORDER BY ?

--the...

Dpends on the queries you build.  Many of the system views join many tables, many times even in some cases.  So if you can avoid that and do a clustered...

We need the table definition, sample data, the required output from that sample data.  We also need to know what you define as the middle rows.

And knowing for which cool...

Nope, you pretty much just answered your own question.  I'm sure that there are other factors to consider in the equation but it looks like you got the key ones...

It works.  But the session times out after like 10-15 minutes.  So you better make sure you have a pasted copy somewhere before hitting post.

In the 2nd plan, I guess that the computing is done previously to the query and that it doesn't need to be redone afterwards.

But that's still and interesting point!

Here's a question : What happens if during that select, the @@identity value is changed by another process?  Does the plan consider it to be a scalar value that will never...

Always the last one .