l******9
发帖数: 579 | 1 【 以下文字转载自 JobHunting 讨论区 】
发信人: light009 (light009), 信区: JobHunting
标 题: SQL select one value column for each distinct value another col
发信站: BBS 未名空间站 (Fri Jun 27 10:29:25 2014, 美东)
On SQL server 2008 R2, I would like to select one value of a column for each
distinct value of another column.
e.g.
name id_num
Tom 53
Tom 60
Tom 27
Jane 16
Jane 16
Bill 97
Bill 83
I need to get one id_num for each distinct name, such as
name id_num
Tom 27
Jane 16
Bill 97
For each name, the id_num can be randomly picked up as long as it is
associated with the name.
For example, for Bill, I can pick up 97 or 83. Either one is ok.
I do know how to write the SQL query.
Thanks | a**d
发帖数: 4285 | 2 刚查的，周五下午，做题为找工作准备。
select col1, col2
from
(
select col1, col2,
ROW_NUMBER() over (partition by col1 order by newid())rn
from table1
)temp_t
where rn=1
each
【在 l******9 的大作中提到】
: 【 以下文字转载自 JobHunting 讨论区 】
: 发信人: light009 (light009), 信区: JobHunting
: 标 题: SQL select one value column for each distinct value another col
: 发信站: BBS 未名空间站 (Fri Jun 27 10:29:25 2014, 美东)
: On SQL server 2008 R2, I would like to select one value of a column for each
: distinct value of another column.
: e.g.
: name id_num
: Tom 53
: Tom 60
|
|
