Excellent, really interesting article!

Solomon Rutzky (12/29/2010)

---

Adam Machanic (12/29/2010)

---

Have you compared the performance of doing a median calculation w/compression against performing the same calculation using Joe Celko's ROW_NUMBER technique?

http://sqlblog.com/blogs/adam_machanic/archive/2006/12/18/medians-row-numbers-and-performance.aspx

Compression is far from cheap, and...

LutzM (12/15/2010)

---

billj-705548 (12/15/2010)

---

Thank you for that method as well Mark. If I'm understanding correctly, this method counts the number of ordinals before the current one -- though I've got...

mike 57299 (12/15/2010)

---

Mark-101232 (12/15/2010)

---

INSERT INTO hdr(id,pk,notes)

SELECT ROW_NUMBER() OVER(ORDER BY a.fk),

a.fk,

STUFF((SELECT ' / ' + RTRIM(b.dtl_note) AS...

INSERT INTO hdr(id,pk,notes)

SELECT ROW_NUMBER() OVER(ORDER BY a.fk),

a.fk,

STUFF((SELECT ' / ' + RTRIM(b.dtl_note) AS "text()"

...

Another way

SELECT

doc.col.value('../@ParameterName', 'varchar(100)') ParameterName,

doc.col.value('.', 'varchar(max)') pv,

doc.col.value('count(for $a in . return $a/../pv[.<<$a])+1', 'int') SortOrder

FROM @xml.nodes('/ParameterList/Parameter[@ParameterName="Make"]/pv') doc(col)

Is the XQuery ceiling function allowed?

WITH TESTDATA(Number) AS (

SELECT 12307.96 UNION ALL

SELECT 2207.96

),

XMLData(data) AS (

SELECT Number AS "@Val"

FROM TESTDATA

FOR XML PATH('X'),ROOT('R'),TYPE

)

SELECT r.value('@Val','float') AS Number,

...

Try using Max

Select Jaar,

MAX(Case When Koersnaam='Tour de France' Then Coureur End) As [Tour de France]

MAX(Case When Koersnaam='Giro d''Italia' Then Coureur End) As [Giro d'Italia]

etc.

FROM SomeView

GROUP BY Jaar

ORDER BY Jaar;

luissurf_70 (12/9/2010)

---

Hi!!! I really sorry... I forget one thing... The quantities includes returns and the returns quantities are stored as positive numbers like this:

TrxId ItemId Date...

-- Note you can use PIVOT for this as well

DECLARE @T TABLE(TrxId VARCHAR(10), ItemId VARCHAR(10),[Date] DATETIME, Quantity INT)

INSERT INTO @T(TrxId,ItemId,[Date],Quantity)

SELECT 'Sales001','ItemA','20100101' ,2 UNION ALL

SELECT 'Sales002','ItemB','20100101' ,4 UNION ALL

SELECT...

SELECT BloggerName,Topic,[Year],Total,

Rank() OVER (Partition by BloggerName Order by Total DESC) as 'Ranking',

COUNT(*) OVER(PARTITION BY BloggerName) AS BloggerNameCount

FROM BlogCount;

<Snip>Previous post covered it</Snip>

Not sure if you've tried this already.

DECLARE @MyTable TABLE(MYKEY INT,DT VARCHAR(10),A INT,B INT,C INT,D INT,E INT);

INSERT INTO @MyTable(MYKEY,DT,A,B,C,D,E)

SELECT 1 ,'1Jan', 1, 2, 3, 4, 5 UNION ALL

SELECT 1 ,'2Jan', 6,...

Try this

CREATE TABLE #temp (

id int primary key identity(1,1) not null

,actionId int not...

--Using slow triangular join

SELECT a.linenum,

a.fileline,

COUNT(b.linenum)+1 as group_id

FROM #TMP_LOADER a

LEFT OUTER JOIN #TMP_LOADER b ON b.linenum<=a.linenum

...