Re: SQL question -- sampling

twallace@mason1.gmu.edu (Todd A Wallace) writes: > I am trying to write an SQL statement that returns some fraction of all > the rows it would normally return. For instance, > > SELECT firstname, lastname, city, state > FROM bigdatabase > WHERE state = "TX"> > will obviously return lots and lots of rows. But what if I just want a > representative sample, say, 1/10 of all rows, or just 100 rows, randomly > selected from the entire returned set? > > Anyone got a good trick out there? One idea that comes to mind is to add something like: AND HEX(rowid) matches "0x??????01" which would retrieve the first row, actually the first slot, from every data page. Or: AND HEX(rowid) matches "0x?????[13579BDF]01" which would retrieve the first row from the odd numbered data pages. You would have to adjust the matches clause depending of the average number of rows on your data pages to come up with the desired sample size. -- Daniel Wood daniel@starsight.com or dwood@informix.com The opinions expressed herein are those of myself, my employers, the carriers of this news message or any other messages for that matter, you the reader and any of your relations(whether by birth or chromosome count), all life forms and all inanimate objects.