Andy Colson wrote > On 4/3/2014 11:09 AM, David Johnston wrote: >> Andy Colson wrote >>> On 4/3/2014 10:27 AM, Schnabel, Robert D. wrote: >>>> I'm trying to figure out how to count the number of rows within a fixed >>>> range of the current row value. My table looks like this: >>>> >>>> SELECT chr_pos >>>> FROM mutations_crosstab_9615_99 >>>> WHERE bta = 38 >>>> LIMIT 10 >>>> >>>> chr_pos >>>> 138 >>>> 140 >>>> 163 >>>> 174 >>>> 187 >>>> 187 >>>> 188 >>>> 208 >>>> 210 >>>> 213 >>>> >>> >>> This is the answer I got, which is different than yours, but I think its >>> right. >>> >>> >>> chr_pos | count >>> ---------+------- >>> 138 | 2 >>> 140 | 2 >>> 163 | 2 >>> 174 | 4 >>> 187 | 3 >>> 188 | 4 >>> 208 | 5 >>> 210 | 4 >>> 212 | 4 >>> 213 | 4 >>> (10 rows) >> >> Same concept as mine - but I'm not sure where the "212" came from and you >> did not duplicate the "187" that was present in the original. >> >> The OP wanted to show the duplicate row - which yours does and mine does >> not >> - but depending on how many duplicates there are having to run the same >> effective query multiple times knowing you will always get the same >> result >> seems inefficient. Better to query over a distinct set of values and >> then, >> if needed, join that back onto the original dataset. >> >> David J. >> > > > > > Same concept as mine - but I'm not sure where the "212" came from and > you > > did not duplicate the "187" that was present in the original. > > Ah, data entry error. I didn't even notice. Oops. > > > The OP wanted to show the duplicate row - which yours does and mine > does not > > Did you post a sql statement? I didn't seem to get it. > > > - but depending on how many duplicates there are having to run the same > > Agreed. If there are a lot of dups, we could probably speed this up. > > -Andy My original seems to be held up for some reason... Let me try again: WITH val (value) AS ( VALUES (138),(140),(163),(174),(187),(187),(188),(208),(210),(213) ) SELECT value, (SELECT count(*) FROM val AS valcheck WHERE valcheck.value BETWEEN src.value - 20 AND src.value + 20) FROM ( SELECT DISTINCT value FROM val ) src ORDER BY 1; -- View this message in context: http://postgresql.1045698.n5.nabble.com/window-function-help-tp5798526p5798565.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
