Your example dates are weird and you are missing the case when there is a
cat and a cat with the same birthdate (which I presume you don't want).
Here is how I would do it in postgres
CREATE TABLE animals
(
id integer,
type text,
bday timestamp without time zone
);
insert into animals (id, type, bday) values
(1, 'Dog', '2018-01-30'),
(2, 'Dog', '2018-02-24'),
(3, 'Cat', '2018-02-24'),
(4, 'Dog', '2018-03-30'),
(5, 'Cat', '2018-04-30'),
(6, 'Cat', '2018-04-30');
with distinct_animals as (select distinct on (type, bday) id, type, bday
from animals)
select bday from distinct_animals group by bday having count(bday) > 1
The "with" clause eliminates all the duplicate cats born on the same date
so that on each date there are only one dog and one cat. Once you have
that you just pull the dates you need which are dates with both a cat and a
dog in them.
On Thu, Feb 1, 2018 at 10:45 AM, Max Farrar <[email protected]> wrote:
> Say I have a model Animal, which has a string 'type', and a time
> 'birthday'.
>
> I want to get a list of only birthday times where there's both a type
> 'dog' and a type 'cat' with birthdays at the time. Is there an optimized
> way to do this using a single Sequel command?
>
> Direct example of data:
>
> ID 1 - Type "Dog" - Birthday "1-31-18"
>
> ID 2 - Type "Dog" - Birthday "2-31-18"
>
> ID 3 - Type "Cat" - Birthday "2-31-18"
>
> ID 4 - Type "Dog" - Birthday "3-31-18"
>
> ID 5 - Type "Cat" - Birthday "4-31-18"
>
>
>
> I'd want to return a list including ID's 2 and 3 only.
>
> --
> 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.
>
--
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.
