On Thu, Apr 21, 2011 at 11:20 PM, Sachin Srivastava
<sachin.srivastava@enterprisedb.com> wrote:
A naive and simple solution is: (Let the table name is table1)
SELECT a.Team, a.Score FROM table1 a, table1 b WHERE a.Score = b. Score and a.Team != b.Team;
On Apr 21, 2011, at 10:35 PM, JORGE MALDONADO wrote:
If I have a table that has a column where values can be the same, how can I SELECT them?
For example, consider the following table:
--------------------------------------
Team Score
--------------------------------------
T1 500
As you can see, T1 and T5 have a score of 500; and T3 and T7 have a score of 505. What I want is a SELECT to get only these four records.
Respectfully,
Jorge Maldonado
--
Regards,
Sachin Srivastava
Although the above suggested solution is good in current context, you should also consider if there are any indexes on either of columns. If there are any indexes, then the inequality predicate might not allow usage of indexes.
(roughly inequality is not used by index scan whereas equality, range predicates are good for index scans)
So somehow the inequality condition has to be converted to range or similar predicates that will benefit indexes usage.
typically you can use NOT IN, NOT EXISTS or even UNION using < and > , while keeping the same logic.
Continuing with above query, we can make use of UNION with range predicates, which might choose index scan depending on data/statistics, as:
SELECT a.Team, a.Score FROM table1 a, table1 b WHERE a.Score = b. Score and a.Team > b.Team
UNION
SELECT c.Team, d.Score FROM table1 c, table1 d WHERE c.Score = d. Score and c.Team < d.Team;
The logic of predicates remains same, but we allow optimizer to choose index path, provided if indexes were available.
But with the previously suggested query, the optimizer with its limitations, would not choose index path.
Likewise, you may make use of other logical operators or operations.
There are quite s few discussion titles "slowness of query", "why indexes are not used" on the the performance d-list.
you might want to go through them for better understanding of this scenario.
--
Regards,
Chetan Suttraway
EnterpriseDB, The Enterprise PostgreSQL company.