On 2/9/20 11:44 PM, Rowan Worth wrote:
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
I think Vacuum maintains Row order but not RowID, unless it has been
aliased to a user defined column.
Yes, one solution would be to add another column that can be used either
alone or paired with other columns to allow a join to find the pair of
readings. So modified, the writing application could be updated to write
the same value into that column for the two readings, and/or a fix-it
app could be run to add it to existing data.
SQL itself is unlikely to have a simple way to do this, as the problem
isn't solvable in general, which is what the SQL solution would be
attempting, unless the SQL solution was basically doing the fixup
operation, finding the duplicate ID/Date rows and adding the pairing value.
--
Richard Damon
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
