Afraid so, 'WITH' is SQL Server 2005 only and is used for CTEs (common table expressions).

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

with CTE1 as

(select EmpID, LocationID,

count(*) over(partition by EmpID,LocationID) as cn,

sum(Amount) over(partition by EmpID) as...

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

If you register your 32 bit VB6 COM object as a COM+ component you should be able to instantiate it in SQL 2005 x64

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

To be honest I wouldn't use any, instead I'd use a range table such as below, avoids hardwiring values

create table Ranges(GrossPayMin decimal(10,3) not null,

...

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Maybe this

WITH CTE AS

(SELECT ID, CUSTPHONENO, EVENTTIME, DUEDATE, ASSIGNEDFORDATE,

ROW_NUMBER() OVER(PARTITION BY ID ORDER BY EVENTTIME DESC) as rn

FROM tbl_table1)

SELECT ID, CUSTPHONENO, EVENTTIME,...

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Try searching for "InitCap"

Here's a simple C# implementation

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Globalization;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlString InitCap(SqlString...

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

You can avoid looping by using a numbers table

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

declare @s-2 varchar(100)

set @s-2='aa,bb,cc,dd,ee,ff,gg,hh,ii,jj';

with cte as

(select Number,row_number() over(order by Number) as occurance

from Numbers

where Number between 1 and len(@s)

...

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Using a table of numbers as in

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

DECLARE @str VARCHAR(100)

SET @str='1, 2, 3, 4, 5, 50, 99 ,100'

SELECT CAST(SUBSTRING(@str,

...

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Here's another way

SELECT R_id,ProductCode,FilterDesc,RowsCount,FilterCount,

SUM(RowsCount) OVER(PARTITION BY FilterDesc) AS SUMROWCOUNT

FROM @SAMPLE

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Untested, but this should work

WITH CTE AS

(SELECT MCID, Date, SepCode,

ROW_NUMBER() OVER(PARTITION BY MCID ORDER BY Date DESC) as rn

FROM Employment)

SELECT MCID, Date,...

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Try this

DECLARE @RootID INT

SET @RootID=12;

WITH CTE AS(

SELECT GroupID,ParentGroupID

FROM AframeGroup

WHERE ParentGroupID=@RootID

UNION ALL

SELECT a.GroupID,a.ParentGroupID

FROM AframeGroup a

INNER JOIN CTE c ON c.GroupID=a.ParentGroupID)

SELECT GroupID

FROM CTE

ORDER BY GroupID

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Here's another way

DECLARE @MyTable TABLE

(

ID INT,

RowNumber INT,

RowText NVARCHAR(20)

)

INSERT INTO @MyTable

SELECT 12, 1, 'ABC-' UNION

SELECT 12, 2, 'XYZ' UNION

SELECT 12, 3, 'DEF' UNION

SELECT 13, 1, 'ABC' UNION

SELECT 13, 2, 'DEF' UNION

SELECT...

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Try this

with cte

as (select row_number() over(partition by name order by Eff_date desc) as rn

from mytable)

delete from cte

where rn>1

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537