It's much easier to load all the data into a in-memory hierarchical structure and work with it from memory than try to come up with individual SQL statements. Some things you could do easily like find the leaf nodes (i.e., use a sub-select and identify nodes that nobody else uses as a parent) or find the root nodes (parent id is null) but getting a full hierarchy requires multiple SQL statements and thus is faster to process in a custom hierarchical structure rather than through SQL.
Also note that using "version DESC" will not necessarily give you the expected results because it's using string comparison and '10.0' is less than '2.0' with string comparison. You could create a custom collation routine to do proper version number comparison but it'd be faster to store the data as separate integers (a field for major and minor version). HTH, Sam ------------------------------------------- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -----Original Message----- From: Clark Christensen [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 20, 2007 1:57 PM To: SQLite List Subject: [sqlite] Finding linked peers I'm having trouble wrapping my head around a solution here. Any advice is appreciated. I'm working on a SQLite-based app for keeping track of PC BIOS releases. One obvious requirement is to be able to track and document change history. So, using this sample schema/data code: create table bios_release ( oid integer primary key, parent_id integer, group_id integer, version text, bios_name text ); insert into bios_release values (1, null, 1, '1.10', 'PC1_v1.1'); insert into bios_release values (2, null, 2, '1.10', 'PC2_v1.1'); insert into bios_release values (3, 1, 1, '1.20', 'PC1_v1.2'); insert into bios_release values (4, null, 3, '1.10', 'PC3_v1.1'); insert into bios_release values (5, 3, 1, '1.30', 'PC1_v1.3'); /* irrelevant to the discussion, but provided for clarity (?) */ create table bios_group ( oid integer primary key, group_name text ); insert into bios_group values (1, 'PC1 Group'); insert into bios_group values (2, 'PC2 Group'); insert into bios_group values (3, 'PC3 Group'); I see two ways to go to know which releases. The obvious way is to simply maintain a history group with an ID number that gets stored in each member BIOS release record. A simple: select * from bios_release where group = 1 order by version desc; will get me all the members of the PC1 Group in descending order. That's fine, but, creating, naming, and maintaining the groups is a hassle for the administrators. The other option I'm considering is chaining each new bios_release to its predecessor by storing the predecessor OID in the parent_id column. Doing it this way makes some sense from the admin process POV because usually, the admin has a new BIOS with a set of known properties, and not necessarily any knowledge of what group it might belong to, or whether or not she needs to create a new group for this BIOS. So, finally, the question: What might the SQL look like to retrieve a list of predecessors for 'PC1_v1.3'? Sure, I could prepare a stmt in the app (Perl), and walk the chain in some fashion. Is this result even possible using plain SQL? TIA -Clark ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
