Re: How to group this?

Sorry, I made a typo. The windows should be ORDER BY ROWID, not ORDER BY
ID.

Jen

On Mon, Feb 10, 2020 at 09:19:59AM -0700, Jen Pollock wrote:
> I think the following works:
> 
>  SELECT s.ID 'ID', s.Date Date, Systolic, Diastolic
>  FROM
>  (SELECT ID, ENTRY_DATE Date, NUMERIC_VALUE Systolic, row_number() OVER id_date r
>   FROM pressure
>   WHERE TERM = 'Systolic'
>   WINDOW id_date AS (PARTITION BY ID, ENTRY_DATE ORDER BY ID)
>  ) s
>  JOIN
>  (SELECT ID, ENTRY_DATE Date, NUMERIC_VALUE Diastolic, row_number() OVER id_date r
>   FROM pressure
>   WHERE TERM = 'Diastolic'
>   WINDOW id_date AS (PARTITION BY ID, ENTRY_DATE ORDER BY ID)
>  ) d
>  ON s.ID = d.ID AND s.Date = d.Date AND s.r = d.r
>  ORDER BY s.ID, s.Date;
> 
> You will need a fairly recent version of SQLite, older versions don't
> have window functions. And I have assumed that there is a Diastolic
> value for every Systolic value and vice versa.
> 
> Jen
> 
> On Mon, Feb 10, 2020 at 12:24:33AM +0000, 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
> > _______________________________________________
> > 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