On Monday, December 3, 2018 at 1:54:54 PM UTC-8, Eric Promislow wrote:
>
> Hi,
>
> We want to find all but the most recent processes for each app based on
> the :created_at field. Here's the raw SQL that works:
>
> mysql> select distinct p1.id
> from processes p1 join processes p2
> where p1.app_guid = p2.app_guid and
> p1.created_at < p2.created_at;
> +----+
> | id |
> +----+
> | 1 |
> | 2 |
> +----+
> 2 rows in set (0.01 sec)
>
> What's a good way to express this in Sequel?
>
Not familiar with that MySQL syntax, but I'm guessing it is a CROSS JOIN,
which you can handle by just using multiple FROM tables (Sequel also
supports the CROSS JOIN syntax explicitly):
DB.from{[processes.as(:p1), processes.as(:p2)]}.
where{(p1[:app_guid] =~ p2[:app_guid]) & (p1[:created_at] <
p2[:created_at])}.
distinct.
select{p1[:id]}
# SELECT DISTINCT `p1`.`id`
# FROM `processes` AS `p1`, `processes` AS `p2`
# WHERE ((`p1`.`app_guid` = `p2`.`app_guid`)
# AND (`p1`.`created_at` < `p2`.`created_at`))
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.
For more options, visit https://groups.google.com/d/optout.
