I'm told that ROW_NUMBER
is the best way to select the top row from each group, but it doesn't work. Here is my example
SELECT
TOP 10000
CASE WHEN var3 IN ('string1', 'string2') THEN 'Y' ELSE 'N' END AS PosDiag,
var1, var2, var3, dat4,
ROW_NUMBER() OVER (PARTITION BY var1 ORDER BY PosDiag DESC) rn
FROM
mydb
ORDER BY var1, PosDiag DESC, dat4 ASC
This just throws an error:
Invalid column name 'PosDiag'
If I remove PosDiag from the ORDER BY
it still throws the error. At some point I have to order by both var1 and then PosDiag to bring the PosDiag = 'Y' to the top of each group, when/where it exists, so that the newly created row number will always correspond to row number 1 and can later be extracted.
CodePudding user response:
You can't reference aliases defined in the SELECT clause in other alias definitions, etc.
You need to define the alias in a sub-query in order to use it in other expressions.
SELECT
TOP 10000
posDiag,
var1, var2, var3, dat4,
ROW_NUMBER() OVER (PARTITION BY var1 ORDER BY PosDiag DESC) rn
FROM
(
SELECT
mydb.*,
CASE WHEN var3 IN ('string1', 'string2') THEN 'Y' ELSE 'N' END AS PosDiag
FROM
mydb
)
mydb
ORDER BY
var1,
PosDiag DESC,
dat4 ASC
Also, bear in mind that SQL is a Declarative Language. You write an expression and then it is parsed and 'compiled' in to an execution plan. Just because you have a sub-query to fit the language doesn't mean that the sub-query adds un-necessary processing.
EDIT:
Alternative forms...
WITH
mydb AS
(
SELECT
mydb.*,
CASE WHEN var3 IN ('string1', 'string2') THEN 'Y' ELSE 'N' END AS PosDiag
FROM
mydb
)
SELECT
TOP 10000
posDiag,
var1, var2, var3, dat4,
ROW_NUMBER() OVER (PARTITION BY var1 ORDER BY PosDiag DESC) rn
FROM
mydb
ORDER BY
var1,
PosDiag DESC,
dat4 ASC
or...
SELECT
TOP 10000
posDiag,
var1, var2, var3, dat4,
ROW_NUMBER() OVER (PARTITION BY var1 ORDER BY PosDiag DESC) rn
FROM
mydb
CROSS APPLY
(
SELECT CASE WHEN var3 IN ('string1', 'string2') THEN 'Y' ELSE 'N' END
)
AS translate_var3(PosDiag)
ORDER BY
var1,
PosDiag DESC,
dat4 ASC
CodePudding user response:
After you set the value for PosDial, try replacing it (PosDiag) with its content (CASE WHEN var3 IN ('string1', 'string2') THEN 'Y' ELSE 'N' END), as it is an alias and its value is not known within the same query:
SELECT
TOP 10000
CASE WHEN var3 IN ('string1', 'string2') THEN 'Y' ELSE 'N' END AS PosDiag,
var1, var2, var3, dat4,
ROW_NUMBER() OVER (PARTITION BY var1 ORDER BY CASE WHEN var3 IN ('string1', 'string2') THEN 'Y' ELSE 'N' END DESC) rn
FROM
mydb
ORDER BY var1, CASE WHEN var3 IN ('string1', 'string2') THEN 'Y' ELSE 'N' END DESC, dat4 ASC