Hi, I am emailing to ask if someone could advise me how to create a query or queries which will collapse some data based on a limited number of constraints. I am currently attempting to complete this task using DB Browser for SQLite. I have tried to write a WITH RECLUSIVE statement as I think this is the requirement but am struggling with both the abstraction and syntax.
I have data contained with a single table of structure: CLS1 field (text) CLS2 field (integer) START field (integer) END field (integer I need to collapse the data based on the matching of values in fields CLS1 and CLS2; the final constraint is that if END and START values are continuous of the another record then they can be collapsed. Therefore records should only be collapsed if gaps do not exist. The results then need to written to a new table, leaving the original data as is. Input data: assumptions 1. Data may or may not be ordered 2. Duplicates may or may not exist 3. Start and end values could be the same 4. Start values are normally lower that the end value, however the high value could be in the start field 5. Assume that there is no overlap in terms of start and end values (namely if they can be joined then one will stop where the next starts) Input data: example CLS1,CLS2,START,END ABC1,100,0,1 ABC1,100,1,1 (start and end values could be the same, in the first instance assume that they may be dissolved if possible, if they cannot the record need to be retained) ABC1,100,1,3 ABC1,100,1,3 (duplicates may or may not be present, if present then they can be dissolved into a single instance) ABC1,100,3,4 ABC1,100,4,3 ABC1,100,5,6 ABC1,100,6,20 ABC1,100,6,20(duplicates may or may not be present, if present then they can be dissolved into a single instance) ABC1,500,4,19 ABC1,500,19,4 (start and end values could be inverted where Start is high and End id low (this is not the norm but it is legitimate) in this case start and end values may be inverted, and in this case dissolved into a single instance) ABC2,300,4,4 (start and end values could be the same, in the first instance assume that they may be dissolved if possible, if they cannot the record need to be retained) Output data: collapsed/merged expected output CLS1,CLS2,START,END ABC1,100,0,4 ABC1,100,5,20 ABC1,500,4,19 ABC2,300,4,4 I would be extremely grateful if anybody could help me with this issue. Regards, Rob **************************************************************************************************************************************************************** The content of this email (and any attachment) is confidential. It may also be legally privileged or otherwise protected from disclosure. This email should not be used by anyone who is not an original intended recipient, nor may it be copied or disclosed to anyone who is not an original intended recipient. If you have received this email by mistake please notify us by emailing the sender, and then delete the email and any copies from your system. Liability cannot be accepted for statements made which are clearly the sender's own and not made on behalf of Network Rail. Network Rail Infrastructure Limited registered in England and Wales No. 2904587, registered office Network Rail, 2nd Floor, One Eversholt Street, London, NW1 2DN **************************************************************************************************************************************************************** _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
