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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
