w**********1
发帖数: 17 | 1 Need to replace Field_A with bottom layer of data in Field_B in Table_X.
Wanted to use the following -
UPDATE Table_X
SET Field_A=
(SELECT TOP(1) Field_B FROM Table_X ORDER BY Field_C DESC)
GO
However 'order by' is not allowed in sub-query. What could be the workaround
? Thanks!! |
B*****g
发帖数: 34098 | 2 CTE
workaround
【在 w**********1 的大作中提到】
: Need to replace Field_A with bottom layer of data in Field_B in Table_X.
: Wanted to use the following -
: UPDATE Table_X
: SET Field_A=
: (SELECT TOP(1) Field_B FROM Table_X ORDER BY Field_C DESC)
: GO
: However 'order by' is not allowed in sub-query. What could be the workaround
: ? Thanks!!
|
s**********o
发帖数: 14359 | |
y****w
发帖数: 3747 | 4 first_value
workaround
【在 w**********1 的大作中提到】
: Need to replace Field_A with bottom layer of data in Field_B in Table_X.
: Wanted to use the following -
: UPDATE Table_X
: SET Field_A=
: (SELECT TOP(1) Field_B FROM Table_X ORDER BY Field_C DESC)
: GO
: However 'order by' is not allowed in sub-query. What could be the workaround
: ? Thanks!!
|
w**********1
发帖数: 17 | 5 Thanks much for all your responses! |
B*****g
发帖数: 34098 | 6 贴最终解决方案
【在 w**********1 的大作中提到】
: Thanks much for all your responses!
|
j*******n
发帖数: 48 | 7 UPDATE Table_X
SET Field_A=
(
SELECT TOP(1) Field_B
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY Field_C DESC) as row_num, Field_B
FROM Table_X) t
WHERE t.row_num = 1
) |
z**********8
发帖数: 2049 | 8 这个题目的具体应用：
1。 给最底层 level的 员工 加薪 10％，或者bonus 15%;
2。 引申，给所有manager level的 bonus 20％;
3。 如果 一个 公司 出售 整机，sector， 和 parts，如果给 所有 parts 零售价加
价5％。
这个题的知识点：
1。 update
2. top()
2。row_number()
3. hierarchy 问题的 解决方案。
请补充。 |
