Re: How to group this?

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 <[email protected]>
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 <[email protected]>
> 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
>> [email protected]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users