Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shox
Starting Member
3 Posts |
 Posted - 2009-12-13 : 16:28:06
|
| Hi All,I have a quick question.I am trying to select the top 50% records of a table, of a certain code, but am finding it difficult.I have a table, with about say 10 different codes, and i need to change the codes for 50% of each cell, and ensure all records are selected randomly.I have done the second bit, but I cant seem to get SQL to only select half the records per cell (instead of hardcoding it).EG:Cell Counts1 6002 6153 6124 5555 200I have done the following:select records = (select count(*) from table where code = '1')*0.5 which gives me the correct count (all be it, not rounded).I have tried to incorporate this in a select statement, but it doesn't work.select top (select records = (select count(*) from table where code = '1')*0.5 ) *from tableDoes anyone know a way round this (and also, if it can be rounded to the earest number will be really helpful).Thanks |
|
|
shox
Starting Member
3 Posts |
Posted - 2009-12-13 : 16:59:33
|
| Figured it out, solution was simple enuff |
 |
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2009-12-14 : 01:00:52
|
| Try like thisselect * from(select row_number() over(order by NewID()) as s_no,* from table_name )awhere s_no <= (select floor(count(*)*0.5) from table_name where code =1)Senthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/ |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-12-14 : 01:29:30
|
quote:
Originally posted by shox
Figured it out, solution was simple enuff
Post your solutionMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Selecting top records, using calculations