cms9651 (9/6/2012)

---

The rule for the sorting is mat geographic NAME.

MAW = My Area West

MAE = My Area East

MAC = My Area Center

MAS = My Area South

... I am stumped on why that is not working...

...

True but as I said there seems to be a bit of clarity needed on what the order should be. The MAT column is not ordered in the desired output.

Luis Cazares (9/6/2012)

---

Using Eugene suggestion (@index is declared int to prevent injection, but might create errors):

...

[/code]

Sean Lange (9/6/2012)

---

...

This query will produce the same results:

SELECT

COALESCE ([MAT], 'Tot') AS [MAT],

[DVD],

SUM ([SALES])

FROM

TestTable

GROUP BY

(MAT),

[DVD] --with rollup

ORDER BY

[DVD] DESC

...

Eugene Elutin (9/6/2012)

---

MVDBA (9/6/2012)

---

eugene... just out of interest... why avoid the windowing???

Because using them may be slower then implementation without them...

mssqlsrv (9/6/2012)

---

I think you can not use variable in order by clause.

Variables are only allowed when ordering by an expression referencing a column name.

MVDBA (9/6/2012)

---

example 2 wont work -you'll end up with a table called demo1 not demo

MVDBA (9/6/2012)

---

eugene... just out of interest... why avoid the windowing???

Luis Cazares (9/6/2012)

---

IMO, different queries, same result. Just like:

SELECT CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) AS one

and

SELECT 1 one