Select Top 1 MySql

user02769

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

MarkSnodgrass

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.

MarkSnodgrass

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)

user02769

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

MarkSnodgrass

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.

user02769

Thank you @MarkSnodgrass

jaeW_at_Onyx

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!

user02769

@jaeW_at_Onyx

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?

jaeW_at_Onyx

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