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