Re: window function help

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