DOMO SQL MAX(date) not working
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'
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?
@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.
SELECT Count(*) FROM `table` WHERE a_date is not null and id is not null and name is not null
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"0
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
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!?
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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0
- 10.5K All Categories
- 5 Connect
- 915 Connectors
- 250 Workbench
- 459 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 185 Visualize
- 250 Beast Mode
- 2.1K Charting
- 11 Variables
- 77 Cards, Dashboards, Stories
- 16 Automate
- 354 APIs & Domo Developer
- 88 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 245 Distribute
- 62 Domo Everywhere
- 242 Scheduled Reports
- 20 Manage
- 41 Governance & Security
- 170 Product Ideas
- 1.2K Ideas Exchange
- 10 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive