Here is the version using NOT EXISTS.

WITH sq AS (

SELECT

RelationshipID

,ParentID

,ChildID

,RoleID

,StatusID

,CreateDate

,ActionID

,SelectOrNot

,ROW_NUMBER() OVER (PARTITION BY ChildID ORDER BY CreateDate) AS sort

FROM @RoleRanking AS rr

WHERE NOT EXISTS (

SELECT *

FROM @RoleRanking AS sub

WHERE sub.ChildID...

I've found that the windowed aggregates do a LOT of reads. You might be better off using a NOT...

Actually, the correct answer (assuming that your date fields are actual date fields) is that it's IMPOSSIBLE. Your expected output violates first normal form, because some of the rows...

Assuming that there is some date changed associated with each of the changes, use that to determine which change is more recent.

Option 2 is to update the source of the...

Your criteria for which rows to update in your merge is incomplete. Since you forgot to include the DDL for your tables, sample data, expected results, and your current...

A lot of that may depend...

Someone else recently posted the exact same question, so this looks like a homework assignment. I suggest you try it on your own first.

Drew

captcooldaddy (3/20/2012)

---

or wrap in sinulls

also only the records who need to be update...removed left join

UPDATE t1 SET field1 = t2.field1

FROM Table1 t1 JOIN Table2 t2 ON

t2.id1 = t1.id1

AND t2.id2 =...

You do realize that that comment was made THREE...

captcooldaddy (3/20/2012)

---

also only the records who need to be update...removed left join

UPDATE t1 SET field1 = t2.field1

FROM Table1 t1 JOIN Table2 t2 ON

t2.id1 = t1.id1

AND t2.id2 = t1.id2

where...

Just to be...

I don't know MySQL either, but here is an approach using a Tally table. (I may have the syntax for REGEX wrong.)

SELECT Min(n) AS n

FROM YourTable

INNER JOIN Tally

ON Len(YourTable.YourString)...

It's easy to calculate the beginning of the month using the following function.

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

You can then use that in your CASE expression or WHERE clause as appropriate.

Drew

With only one parameter, the dynamic SQL option is probably overkill. Here is another option that I have seen, which is fine with only one parameter.

IF @StaffID IS NULL...

The error is coming from your Caché database, so you'll probably get a better answer from a Caché forum, but it looks like Caché doesn't support the DATE datatype.

Another...