The WHERE clause doesn't generate an output column. Ever.

There are four well-known ways to calculate running totals...

nguyennd (8/1/2012)

---

because performance, the tables A,B have...

dwain.c (7/31/2012)

---

SQLRNNR (7/27/2012)

---

Really unfortunate - I would love to see more from...

Throw

Abu Dina (7/31/2012)

---

Northwest UK yes 🙂 I will PM you.. seriously you deserve drinks for all this help!!!!!

I've made the change and the function is working better now. 🙂

Going...

Abu Dina (7/31/2012)

---

I see.

Another question if you don't mind (you're going to hate me once this little project of mine is done! but if you're anywhere near the northwest then...

Fix your sample data script which is incomplete and full of errors and your view definition which references a table which doesn't exist and I'd be glad to...

Here's another way:

-- ID of the first row of the dupeset is rn = 1, subsequent dupes have rn > 1

SELECT

ID,

NAME,

[Description],

Location,

StorageRef,

rn = ROW_NUMBER() OVER...

No - it's the closest online reference I could find at the time. The iTVF...

Hello and welcome to ssc. Please read the link in my signature block, this will show you how to set up and post scripts for sample data. You're far more...

Craig Freedman has an excellent description of how recursive CTE's work.

What's important to remember about rCTE's is that only the result of the last recursive iteration (the alias lr)...

Excellent investigative work, Paul.

It's not uncommon for indexes to be disabled during loads - but best practice is to enable them again afterwards 😉

sku370870 (7/31/2012)

---

Apologies - you're right - bit of brain freeze here I guess.

If I sort by List I get:

1>2

1>2>3

1>2>3>6

1>2>3>7

1>2>3>7>10

1>2>3>7>9

1>2>4

1>2>4>8

1>2>5

So, the only niggle is that 1>2>3>7>10 is appearing before 1>2>3>7>9

I've tried...

Put in a specific check for NULL:

SELECT TOP 100 PERCENT

WebFormData_2.FieldValue AS Surname,

WebFormData_7.FieldValue AS SpecialReq

FROM db.R

INNER JOIN db.WebFormData WebFormData_2

ON db.R.WebFormRowId = WebFormData_2.WebFormRowId

AND (WebFormData_2.WebFormFieldId = 98 OR WebFormData_2.WebFormFieldId...

ISNUMERIC isn't very useful, see this article[/url].

(CROSS/OUTER) APPLY can be a handy and efficient alternative:

SELECT

m.BatchFileId,

x.Num1

FROM MyTable m

CROSS APPLY (

SELECT Num1 = CAST(

CASE WHEN m.LineItemValue NOT LIKE '%E%' THEN m.LineItemValue...