Select Top 1 MySql
Does any one know what am doing wrong here? I am trying to convert a sql statement to Domo Mysql but I am getting an error
Original sql:
SELECT
ta.Item
, ta.Warehouse
, ta.ShipDate
, tb.ActivityDate
FROM
copy_of_cuts_w_forecast ta
JOIN
RunningCases tb
ON tb.Item = ta.Item
AND tb.Warehouse = ta.Warehouse
AND tb.ActivityDate = (SELECT TOP 1
ActivityDate
FROM
InventoryAvailability tb
WHERE
Item = ta.Item
AND Warehouse = ta.Warehouse
AND ActivityDate <= ta.ShipDate
ORDER BY
ActivityDate DESC);
How I tried to convert:
SELECT
`Item Trim_Fill Rate`,
`Trim Whse`,
`Ship Date`
FROM
`copy_of_cuts_w_forecast`
JOIN
`inventoryavailability`
ON `Item Trim_Fill Rate` = `Item`
AND `Trim Whse` = `Whse`
AND `ActivityDate` = ( MAX 1
`ActivityDate`
FROM
`RunningCases`
WHERE
`Item` = `Item Trim_Forecast`
AND `Whse` = `Trim Whse`
AND `ActivityDate` <= `Ship Date`
ORDER BY
`ActivityDate` DESC)
end
Best Answer
-
that indicates that the column name item is in more than one table. You can remedy this explicitly stating the table name and column name in this format: tablename.columnnname wherever you are referencing a column. You can also alias a table after declaring it to shorten it when you reference it elsewhere.
For example, you can do the following:
SELECT inv.Item
FROM inventory AS inv
The AS statement allows me refer to the inventory by just using inv. In the SELECT statement, I prefix the Item column with inv to explicitly state that I want the Item column from the inventory table. This is very useful when you are creating a query that involves multiple tables, which is what you are doing.
Hope this helps.
**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.2
Answers
-
Domo uses MySQL 5.6, so any time you wonder what syntax you should use, just google the reference docs for MySQL 5.6. Looks like you need to use the LIMIT clause in MySQL, so your query would need to be changed to this
SELECT
`Item Trim_Fill Rate`,
`Trim Whse`,
`Ship Date`
FROM
`copy_of_cuts_w_forecast`
JOIN
`inventoryavailability`
ON `Item Trim_Fill Rate` = `Item`
AND `Trim Whse` = `Whse`
AND `ActivityDate` = ( SELECT
`ActivityDate`
FROM
`RunningCases`
WHERE
`Item` = `Item Trim_Forecast`
AND `Whse` = `Trim Whse`
AND `ActivityDate` <= `Ship Date`
ORDER BY
`ActivityDate` DESC LIMIT 1)
**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.1 -
Thank you @MarkSnodgrass. I changed the query to what you show and now I am receiving this error. I have not seen this type of error before - any ideas what may be going on?
Column 'Item' in on clause is ambiguous
0 -
that indicates that the column name item is in more than one table. You can remedy this explicitly stating the table name and column name in this format: tablename.columnnname wherever you are referencing a column. You can also alias a table after declaring it to shorten it when you reference it elsewhere.
For example, you can do the following:
SELECT inv.Item
FROM inventory AS inv
The AS statement allows me refer to the inventory by just using inv. In the SELECT statement, I prefix the Item column with inv to explicitly state that I want the Item column from the inventory table. This is very useful when you are creating a query that involves multiple tables, which is what you are doing.
Hope this helps.
**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.2 -
Thank you @MarkSnodgrass
0 -
I know this issue is already resolved
but you should try building this in Magic. It will be SIGNIFICANTLY faster and be much easier to maintain because what you descibed can be done with two tiles. RANK (rownumber) and Filter where Rank = 1
1) from the end user perspective, one of the nice things about Domo is that it lowers the technical barrier to entry. You wrote a relatively complex SQL query that would have been simplified with a RANK function which is easy enough to implement in SQL 5.6 using variables, and in Magic there's a Tile that will recreate
- RowNumber() over (Partition By Item, Whse order by Date Desc
2) Magic will perform faster b/c it automates performance optimization. if you didn't understand the ambiguous column error, I suspect you also didn't index your dataset, which is virtually a required operation for MySQL performance. Even if you did index your dataset, for the next user who supports you, we don't want to make assumptions about their technical skills. In short... just use Magic.
3) the nice thing about recognizing "i want the most recent version of this row" as a function you can resolve in Magic, means that when you need to get around to doing a recursive dataflow, you'll already have the muscle memory that recognizes this problem, and you'll deploy the same pattern!
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 -
Thank you for this input. I did not use Magic because I am not simply trying to look for the most recent date - I am attempting to join two datasets together by date - Where a date exists in my left join and does not exist in my right join, I am coming up with null values. So what I was attempting to do was wherever there is not a match on my date field, I wanted to select the date that is closest to the date field in my left join, rather then just selecting the highest or lowest date. Do you think this can still be done in Magic?
0 -
You can do it in magic but it would be a little sloppy.
MySQL might be easier if you've got the SQL chops.
I would have done a FULL OUTER of JOIN of both date tables. then coaleasce dates from a and dates from B.
then if there is no value in table b use the lag() or lead() function to find the nearest value from table A that's populated. since lag and lead don't exist in MySQL you have to use user defined functions
this is an example of rank but you just do slightly different math for lead / lag.
https://stackoverflow.com/questions/3333665/rank-function-in-mysql
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
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 620 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 742 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive