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 |
|
scramasax
Starting Member
1 Post |
 Posted - 2006-08-31 : 10:10:51
|
| I have a table with a column that has 200 000 000 value where 18 000 000 are distinct. I want to know which value are found more than 50 000 times in the column How would you do it without killing the database with a query that will take too much resource? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-31 : 10:15:08
|
| SELECT MyCol FROM MyTable GROUP BY MyCol HAVING COUNT(*) >= 50000Peter LarssonHelsingborg, Sweden |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-09-01 : 02:22:35
|
| I'd probably do a restore of a backup-file to another non-production database and do my researching there. A 200mill row table is really unforgiving should you make a mistake...Just a slight change to Pesos query that will show you the actual count of each distinct values and not cause any locking:SELECT MyCol, COUNT(*) as Occurances FROM MyTable WITH (NOLOCK)GROUP BY MyCol HAVING COUNT(*) >= 50000--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
|
|
|
Counting popular value in a column