Luis Cazares (5/25/2015)

---

This might be simpler to understand.

WITH CTE AS(

SELECT *, (ROW_NUMBER() OVER(PARTITION BY Country ORDER BY Id)+ 2) / 3 AS rowrank

...

Not totally sure what you're after but this may work

SELECT *,

DATEDIFF(Day,MIN(OrderDate) OVER(PARTITION BY CustomerID),OrderDate) + 1

FROM OrderRanking

ORDER BY OrderID

This should work for you

WITH CTE AS (

SELECT *,ROW_NUMBER() OVER(PARTITION BY Account_ID ORDER BY Priority) AS rn

FROM staging_account)

DELETE FROM CTE

WHERE rn > 1;

Maybe this?

declare @xmldoc as xml= '<Text>This is firstline<Break></Break>This is second line<Break></Break>This is third line</Text>';

declare @break char(1) = char(10);

select @xmldoc.query('for $c in /Text/text() return concat($c,sql:variable("@break"))').value('.','varchar(max)');

Thanks!

This works with your data

WITH CTE AS (

SELECT Hwy, Start, [End], Condition1, Condition2,

ROW_NUMBER() OVER(PARTITION BY Hwy ORDER BY Start, [End]) -

...

WITH CTE AS (

SELECT ItemID, [Version],

ROW_NUMBER() OVER(PARTITION BY ItemID ORDER BY CAST(PARSENAME([Version],3) AS INT) DESC,CAST(PARSENAME([Version],2) AS INT) DESC,CAST(PARSENAME([Version],1) AS INT) DESC) AS rn

FROM...

Change

N'<th>Db_denydatareader</th></tr>' +

to

N'<th>Db_denydatareader</th>' +

A change to the end of Lynns query gives you the concatenated results you're after

with basedata as (

select

[sid],

scode,

cast(stuff(scode,1,0,replicate(N'...

Something like this?

SELECT a.Account, a.[Acct Ref], a.[Txn Set], a.[Txn Ref],

CASE WHEN COUNT(*) = 1 THEN MAX(b.[Account]) ELSE '1 to Many' END AS [Alt...

Have a look here

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Assuming weeknumber is contiguous you can do this

SELECT a.WeekNr,a.Data

FROM mytable a

WHERE EXISTS(SELECT * FROM mytable b

WHERE b.WeekNr...

You're on the right lines with ROW_NUMBER

WITH CTE AS (

SELECT ID ,Total_Used , Date_,

ROW_NUMBER() OVER(PARTITION BY YEAR(Date_),MONTH(Date_) ORDER BY Date_ DESC,Total_Used DESC) AS...

Here's a solution using recursion, don't expect it to be quick though.

WITH Ordered AS (

SELECT m.MACHINE_NUMBER,m.VALUE,m.EVENT_DATE,

ROW_NUMBER() OVER(PARTITION BY m.MACHINE_NUMBER ORDER BY m.EVENT_DATE) AS...

Here's a different approach

WITH RemovedY AS (

SELECT t.ID,

t.CODE,

SUBSTRING(t.CODE,(s.number*5)+1,4) AS CODE2,

...