DOMO SQL MAX(date) not working

Nek
Nek Member
edited March 2023 in SQL DataFlows

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 Coach

    @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`
    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Nek
    Nek Member

    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 Coach

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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Categories