[SQL] ROW_NUMBER() OVER probeem

[Swerfer]

Ik heb onderstaande select query welke prima werkt:

SQL:

SELECT
    ID,
    Name,
    ROW_NUMBER() OVER(ORDER BY Name) AS RowNumber
FROM Users

Nu heb ik de query iets aangepast met 'Name as test', zie onder, maar nu krijg ik de foutmelding 'Invalid column name 'test'

SQL:

SELECT 
    ID,
    Name as test,
    ROW_NUMBER() OVER(ORDER BY test) AS RowNumber
FROM Users

De 2e sql is maar een voorbeeld, mijn echte select is een regel of 40, maar ik moet gebruik maken van de 'column as xxx' verwijzing omdat ik onderstaande sql in mijn select heb waar ik moet kunnen sorteren op 'InTitle'

SQL:

CASE
WHEN Blaat like '%' + @SearchQuery + '%' THEN '0'
ELSE '1'
END as InTitle

Edit:

Onderstaande werkt, maar lijkt me niet de beste oplossing

SQL:

WITH tempTable AS
(
SELECT 
    ID,
    Name as test
FROM Users
)
SELECT
    *,
    ROW_NUMBER() OVER(ORDER BY test) AS RowNumber
FROM
    tempTable

[ Voor 15% gewijzigd door Swerfer op 20-05-2013 13:42 ]

[.KoenR]

Swerfer schreef op maandag 20 mei 2013 @ 13:30:

SQL:

WITH tempTable AS
(
SELECT 
    ID,
    Name as test
FROM Users
)
SELECT
    *,
    ROW_NUMBER() OVER(ORDER BY test) AS RowNumber
FROM
    tempTable

In plaats van bovenstaande CTE is de enige andere optie je "Name" column als een cross apply mee te geven:

SQL:

SELECT
    ID,
    X.InTitle,
    ROW_NUMBER() OVER(ORDER BY X.InTitle) AS RowNumber
FROM Users
  cross apply ( select IIF(Blaat like '%' + @SearchQuery + '%', 0, 1) as InTitle ) as X

[SKiLLa]

Jawel dat klopt; kolomnaam-aliassen zijn voor de buiten de subquery/cte, daarbinnen dien je de gewoon de orginele kolomnaam te gebruiken. Bijvoorbeeld:

SQL:

SELECT
    MyField as MyAlias
FROM
    MyTable
WHERE
    MyField > 1
ORDER BY
    MyAlias

PS: .KoenR: Vermoedelijk is de CROSS APPLY oplossing wel stukken (?) 'duurder' ...

[.KoenR]

Ik vermoed (maar dat is niet te controleren zonder de definitieve data) juist dat de cross apply voordeliger zal zijn in dit geval. Deze wordt altijd nog steeds maar 1x uitgevoerd per row. Mocht hij in dit geval het resultaat nodig hebben in zowel de ROW_NUMBER als een extra kolom moet deze toch 2x berekent worden.

[SKiLLa]

Ik denk dat dat niet opweegt tegen de hoeveelheid data die de '40 regels' subquery meer moet verwerken nu het 'filter' zich buiten de subquery/cte bevindt en niet meer erbinnen. Althans, dat is mijn ervaring met grote datasets ...

[Swerfer]

Ik denk dat ik vooralsnog voor mijn oplossing ga, omdat 'InTitle' niet de enige alias is. Er zijn er in totaal 8, wat dan een hoop 'CROSS APPLY' oplevert. (Wat voor mij overigens nieuw is).