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,

...

Here's another way

SELECT R_id,ProductCode,FilterDesc,RowsCount,FilterCount,

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

FROM @SAMPLE

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,...

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

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...

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