My solution would work for any number of types. It just selects on distinct types and dates first and then checks for dates with more than one type of animal.
Also there is feb 31, don't give invalid dates as your sample data. On Thu, Feb 1, 2018 at 11:52 AM, Max Farrar <[email protected]> wrote: > Hmm.. to clarify: > > 'Dog' and 'Cat' wouldn't be the only type in the data, so I don't think > that example works for my situation. I should have listed other things.. > editing that now. > > On Wednesday, January 31, 2018 at 2:42:02 PM UTC-8, Tim Uckun wrote: >> >> 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. > -- 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.
