DOMO SQL MAX(date) not working

Nek
Nek βšͺ️

hello, hope someone could help on my SQL:

I just created a simple SQL under DOMO MySQL to select some fields and the MIN and MAX of a date, like:

SELECT `id`, `name`, min(`a_date`) as 'oldest date', max(`a_date`) as 'latest date'

FROM`data_1`

GROUP BY `id`, `name`

but nothing is returned in the 2 date fields, original dataset contain a valid date value in all rows.

I tried to simply select the MIN or MAX date without GROUP BY, it works properly, but it returns null once with GROUP BY. Any idea and solution?

thank you!

Tagged:

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟀

    @Nek , Domo MySQL dataflows run on MySQL 5.6 databases. If you write valid MySQL code it should run.

    Your code looks fine so I'm surprised you're not getting results.

    Keep in mind the preview will only show the first 100? rows AND default behavior when building dataflows is for domo to only ingest the first 10k? rows, so maybe in the set of data you're aggregating there are no dates for that particular ID and Name.

    Try

    SELECT Count(*)
    FROM `table`
    WHERE a_date is not null and id is not null and name is not null
    

    Then

    SELECT `id`, `name`, min(`a_date`) as 'oldest date', max(`a_date`) as 'latest date'
    FROM`data_1`
    where a_date is not nullο»Ώ
    GROUP BY `id`, `name`
    
  • Nek
    Nek βšͺ️

    thanks jaeW_at_Onyx for your reply and advice! I did check all records contain a valid date, also tried adding:

    where a_date is not null

    or

    where a_date '1900-01-01'

    but no luck... i just process it ahead and see what the actual outcome would be

    besides, I created the same with ETL, in preview, I can find a date but not get the correct date, e.g. a case with oldest date in 2017 and latest date in 2019, both my created min and max columns show date in 2019...

    not sure if i cannot do both min and max on date at once!?


    thanks.

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon πŸ”΅

    You might try forcing a convert to the date data type to make sure MySQL is properly reading them as dates.

Sign In or Register to comment.