What exactly are you trying to achieve?

You could use NOT EXISTS or a LEFT OUTER JOIN.

The requirement itself is a little unclear since you mention a AcitivityControllerID column that is not part of the result set...

The error most probably points to the column left(Code.Code,3) with a missing alias.

Regarding the first occurence issue: maybe it's possible to filter it out with a WHERE clause or another...

GSquared (5/18/2011)

---

LutzM (5/18/2011)

---

Gus: If this concept...

Brandie Tarvin (5/18/2011)

---

LutzM (5/18/2011)

---

Gus: If this...

Gus: If this concept is expanded...

Something like this?

SELECT a.id 'a', b.id 'b', c.id 'c', d.id 'd', e.id 'e'

FROM Combinations a

CROSS JOIN dbo.Combinations b

CROSS JOIN dbo.Combinations c

...

punia (5/18/2011)

---

Hi Vineet,

I did that way but it do not fulfill the requirement.

It do not work for ID 45. as it assign value 2 to second 'B' rank.

cycle should be...

Duplicate post. No replies please.

Original post: http://www.sqlservercentral.com/Forums/FindPost1110610.aspx

How would you differentiate between

ID Rank Cycle

42 A 1

42 B 1

42 A 2

and

ID Rank Cycle

42 A 2

42 B 1

42 A 1

?

In other words: there is an additional column missing...

It seems like you got trapped by a "semi-optimal" table design...

You might want to look into UNPIVOT to normalize the table. This would make the query much easier and most...

Another option (or expanding one of the alternatives Gus mentioned) would be to use a variable when calling the sproc indicating whether it's the first run or a subsequent call.

In...

Stefan Krzywicki (5/16/2011)

---

LutzM (5/16/2011)

---

...

I think one of the major issues for not being considered is the fact that SQL Server requires a Microsoft OS whereas Oracle and DB2 offer more...

Stefan Krzywicki (5/16/2011)

---

Here's an interesting article on Oracle satisfaction[/url]

SQL Server's not even mentioned, I guess we have a way to go in being considered suitable for very large databases. A...

Just in case you're looking for a solution to recognize both characters (code snippet heavily borrowed from Ron):

DECLARE @T VARCHAR(12)

SET @T = 'xy9^1234567890'

SELECT PATINDEX ( '%9^%' ,@T)

SELECT SUBSTRING(@T,PATINDEX ( '%9^%'...