Solved this now, nil to do with SQL, but just running a different search (other value code and then you can ask for a secondary value and no need anymore to find the matching pair).
RBS On Mon, Feb 10, 2020 at 8:22 AM Bart Smissaert <bart.smissa...@gmail.com> wrote: > I fully agree with you, but I sofar I have no control over this data, I > have it like I showed. > As far as I can see there always will be a secondary value, but as you say > I can't be sure. > All this has to do with changing our clinical coding system from Read > codes to Snomed. > In the old setup there was the concept of a secondary value (systolic >> > diastolic), but it > seems in this particular case that is missing. > I get the data by running searches (not SQL) on a clinical database and I > have no control > over this database. > I will see if I can get better data with a different search, to do with > blood pressure values. > > RBS > > On Mon, Feb 10, 2020 at 3:12 AM 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). >> >> >> -- >> Richard Damon >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
