Sort them by one of the terms (start, for example), then check for overlaps with its (right-hand) neighbor as you move through the list. class tp: def __repr__(self): return “(%d,%d)” % (self.start, self.end) def __init__(self, start, end): self.start = start self.end = end s = [tp(5, 10), tp(7, 8), tp(0, 5)] s.sort(key=lambda self: self.start) y … Read more
Aggregate functions may help you out here. Aggregate functions ignore NULLs (at least that’s true on SQL Server, Oracle, and Jet/Access), so you could use a query like this (tested on SQL Server Express 2008 R2): SELECT FK, MAX(Field1) AS Field1, MAX(Field2) AS Field2 FROM table1 GROUP BY FK; I used MAX, but any aggregate … Read more
There is no default order. Without an Order By clause the order returned is undefined. That means SQL Server can bring them back in any order it likes. EDIT: Based on what I have seen, without an Order By, the order that the results come back in depends on the query plan. So if there … Read more
Sometimes you need to have the ORDER BY in each of the sections that need to be combined with UNION. In this case SELECT * FROM ( SELECT table1.field1 FROM table1 ORDER BY table1.field1 ) DUMMY_ALIAS1 UNION ALL SELECT * FROM ( SELECT table2.field1 FROM table2 ORDER BY table2.field1 ) DUMMY_ALIAS2
LIKE without wildcard character is equivalent to =. Assuming you actually meant name=”text”. Indexes are the key to performance. Test setup CREATE TABLE image ( image_id serial PRIMARY KEY , group_id int NOT NULL , name text NOT NULL ); Ideally, you create two indexes (in addition to the primary key): CREATE INDEX image_name_grp_idx ON … Read more
Let’s say, (7, 10) and (11, 13) result into (7, 13): a = [(7, 10), (11, 13), (11, 15), (14, 20), (23, 39)] b = [] for begin,end in sorted(a): if b and b[-1][1] >= begin – 1: b[-1] = (b[-1][0], end) else: b.append((begin, end)) b is now [(7, 20), (23, 39)] EDIT: As @CentAu … Read more
CodeIgniter’s ActiveRecord doesn’t support UNION, so you would just write your query and use the ActiveRecord’s query method. $this->db->query(‘SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2’);
Here’s a quick little module I wrote that allows you to UNION multiple scopes. It also returns the results as an instance of ActiveRecord::Relation. module ActiveRecord::UnionScope def self.included(base) base.send :extend, ClassMethods end module ClassMethods def union_scope(*scopes) id_column = “#{table_name}.id” sub_query = scopes.map { |s| s.select(id_column).to_sql }.join(” UNION “) where “#{id_column} IN (#{sub_query})” end end end … Read more
There are a number of ways to do this, depending on what you really want. With no common columns, you need to decide whether you want to introduce a common column or get the product. Let’s say you have the two tables: parts: custs: +—-+———-+ +—–+——+ | id | desc | | id | name … Read more