On Mon, 10 Feb 2020 at 11:12, Richard Damon <rich...@damon-family.org> wrote:
> On 2/9/20 7:24 PM, Bart Smissaert wrote: > > ID ENTRY_DATE TERM NUMERIC_VALUE ROWID > > ---------------------------------------------------------------- > > 1308 15/Mar/2013 Systolic 127 701559 > > 1308 15/Mar/2013 Diastolic 81 701568 > > 1308 27/Jun/2013 Systolic 132 701562 > > 1308 27/Jun/2013 Systolic 141 701563 > > 1308 27/Jun/2013 Systolic 143 701564 > > 1308 27/Jun/2013 Diastolic 82 701571 > > 1308 27/Jun/2013 Diastolic 85 701572 > > 1308 27/Jun/2013 Diastolic 94 701573 > > 278975701 08/Mar/2018 Systolic 136 1583551 > > 278975701 08/Mar/2018 Diastolic 99 1583591 > > 278975701 04/Apr/2018 Systolic 119 1583552 > > 278975701 04/Apr/2018 Systolic 124 1583553 > > 278975701 04/Apr/2018 Systolic 130 1583554 > > 278975701 04/Apr/2018 Diastolic 74 1583592 > > 278975701 04/Apr/2018 Diastolic 75 1583593 > > 278975701 04/Apr/2018 Diastolic 85 1583594 > > > > These are systolic and diastolic blood pressures for 2 people with the > ID's > > 1308 and 278975701, ordered by ID asc, ENTRY_DATE asc, ROWID asc. > > Systolic and diastolic values are a pair and should be grouped in one > row. > > This is no problem if there is only one pair for one date, but sometimes > > there multiple pairs per date. > > The pairing should be based on the rowed if there are multiple pairs by > > date, so for ID 1308 > > I should get: > > > > 127/81 > > 132/82 > > 141/85 > > 143/94 > > > > What should be the SQL to group like this? > > > > RBS > > To be honest, I think the problem is fundamentally badly designed. You > say pair the two readings by ROWID, but they of course don't have the > same ROWID, but you seem to be saying to pair them sorted by ROWID (1st > to 1st, 2nd to 2nd, etc). The fundamental problem is what if there isn't > the same number of each? You may say that you know that there will > always be the same number, but there is no constraint that forces this, > so any general program is going to have to deal with the possibility > (and at least throw out an error when it sees that). > Yeah, it would have been easier to group the readings at write time - eg. via another column storing the time of day or the "nth reading of the day". You could still add the latter, post-hoc. Note that ROWID is not persistent -- see quirk #6 here: https://www.sqlite.org/rowidtable.html I would expect that VACUUM's renumbering happens to maintain the row order, but I doubt it's guaranteed. If you do have an INTEGER PRIMARY KEY it's better to refer to that directly. -Rowan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
