由买买提看人间百态

boards

本页内容为未名空间相应帖子的节选和存档,一周内的贴子最多显示50字,超过一周显示500字 访问原贴
Quant版 - SQL fast search in a 10 million records table (转载)
相关主题
SQL find distinct values in large table (转载)How to write this query in Oracle?
SQL combine two columns from two different tables no shared (转载)请教一道面试题啊!关于SQL的!
an interview question(finance)SQL copy a table into a new table and add a new column (转载)
compare two large tables SQL (转载)Pivot table SOS (转载)
SQL multiply all values of a column in table[合集] 面试问题 (转载)
SQL select one value column for each distinct value another (转载)Anyone heard of Conning?
问一下algorithm的书Citadel Investment Group面经 (转载)
keep group of values of SQL procedure in one table (转载)matlab有排名function么?
相关话题的讨论汇总
话题: value话题: column话题: table话题: search话题: sql
进入Quant版参与讨论
1 (共1页)
l******9
发帖数: 579
1
【 以下文字转载自 JobHunting 讨论区 】
发信人: light009 (light009), 信区: JobHunting
标 题: SQL fast search in a 10 million records table
发信站: BBS 未名空间站 (Thu Jul 24 23:26:56 2014, 美东)
I need to do a fast search in a column with floating point numbers in a
table of SQL server 2008 R2 on Win 7.
the table has 10 million records.
e.g.
Id value
532 937598.32421
873 501223.3452
741 9797327.231
ID is primary key, I need o do a search on "value" column for a given value
such that I can find the 5 closest points to the given point in the table.
The closeness is defined as the absolute value of the difference between the
given value and column value.
The smaller value, the closer.
I would like to use binary search.
I want to set an unique index on the value column.
But, I am not sure whether the table will be sorted every time when I search
the given value in the column ?
Or, it only sorts the table one time because I have set the value column as
unique index ?
Are there better ways to do this search ?
Any help would be appreciated.
thanks
c******3
发帖数: 296
2
SELECT TOP 5 abs(value - MYVALUE), id FROM mytable
The table is only sorted once when the index on value column is created.
l******9
发帖数: 579
3
SELECT TOP 5 abs(value - MYVALUE), id
FROM mytable
order by value -- we need this , right ?
Also, the default index sorted tree is ascending ?

【在 c******3 的大作中提到】
: SELECT TOP 5 abs(value - MYVALUE), id FROM mytable
: The table is only sorted once when the index on value column is created.

1 (共1页)
进入Quant版参与讨论
相关主题
matlab有排名function么?SQL multiply all values of a column in table
面试题一道,不会。请高人指教SQL select one value column for each distinct value another (转载)
两个面试题问一下algorithm的书
median number的问题keep group of values of SQL procedure in one table (转载)
SQL find distinct values in large table (转载)How to write this query in Oracle?
SQL combine two columns from two different tables no shared (转载)请教一道面试题啊!关于SQL的!
an interview question(finance)SQL copy a table into a new table and add a new column (转载)
compare two large tables SQL (转载)Pivot table SOS (转载)
相关话题的讨论汇总
话题: value话题: column话题: table话题: search话题: sql