On Tuesday, June 11, 2019 at 9:38:23 AM UTC-7, lkfken wrote:
> Thank you Jeremy. Your example is clear and it works.
>
> Now what if I need to chain this with another CTE? For example
>
> WITH C1 AS (
> ...
> ),
> C2 AS (
>
> SELECT C1.*, se = ROW_NUMBER() OVER (PARTITION BY [MEMBER_ID], [PARENT_GID],
> [TYPE] ORDER BY ts, Type DESC)
> FROM C1
>
> )
> SELECT * FROM [C2]
>
DB[:c2].
with(:c1, DB[:elig].
cross_apply(DB['VALUES (1, START_DATE), (-1, DATE)'].as(:a, [:type,
:ts])).
select(:member_id, :parent_gid, :ts, :type,
Sequel.case({1=>nil},
Sequel.function(:row_number).over(:partition=>[:member_id, :parent_gid,
:type], :order=>:end_date), :type).as(:e),
Sequel.case({-1=>nil},
Sequel.function(:row_number).over(:partition=>[:member_id, :parent_gid,
:type], :order=>:start_date), :type).as(:s))).
with(:c2,
DB[:c1].select_all(:c1).select_append{row_number.function.over(:partition=>[:member_id,
:parent_gid, :type], :order=>[:ts, type.desc]).as(:se)})
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/830c38bd-ffa7-4038-a370-a40edafeec07%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
