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] > <javascript:>> 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] <javascript:>. >> To post to this group, send email to [email protected] >> <javascript:>. >> 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.
