There's another approach that edges out the CROSS APPLY approach. Depending on your indexes, this approach might be better.

PARTITIONED AGGREGATE

WITH OrdersRanked AS (

SELECT [CustomerID],ShipVia

, Sum(Freight) OVER( PARTITION BY CustomerID...

Lowell (10/18/2011)

---

i had a ton of problems getting the function to paste cleanly into the forum, because it contains html codes, so here's a link to the text file of...

I was able to get it to work with the following:

SELECT @Text = Replace(@Text COLLATE LATIN1_GENERAL_BIN, Symbol, HTML)

FROM @Symbols

WHERE PatIndex('%' + Symbol + '%', @Text) > 0

toddasd (10/18/2011)

---

Nice solution, Drew. Very smooth.

OP, if you want the extra non-matches to not show, thrown a WHERE n.Name IS NOT NULL

AND s.Code IS NOT NULL; to the end...

This is actually much simpler than it seems, because SELECT is implemented behind the scenes as a loop. Jeff Moden refers to this as a pseudo-cursor. You can...

This is actually quite easy. You need to order each of the tables separately and then join the two tables both on the segmentID and the order. I...

Actually, the CASE version isn't even completely optimized. It's doing a...

You have two options. Which one works best will depend on a number of factors such as what indexes you have/create and how selective those indexes are.

Option 1: CTE...

Jeff Moden has a very good article on exactly this type of problem.

Solving the Running Total and Ordinal Rank Problems (Rewritten)[/url]

Drew

DELETEs are fully logged, and you're trying to delete 10 million rows IN ONE TRANSACTION. You need to break this up into more manageable pieces. You can use...

The most efficient way to do this is to use the correct tool for the job. That tool is a reporting tool (such as SSRS). T-SQL is most...

This is a presentation issue, and is best done in the presentation layer. You don't mention what you are using for your presentation layer, so we can't give you...

vick12 (10/13/2011)

---

The format of my procedure is as follow: if not converting to varchar in orderby then I ma getting error in ssrs 2005

create stored procedure testsorting

( @sort varchar(60)

)

as

BEGIN

SELECT...

I didn't save the code.

Drew

Actually, neither one is preferred. The first one is worst--as has already been mentioned--because...