MySQL Syntax for MSSQL Query

[mkswanson]

I have the following MSSQL query which I was able to get to run on an MSSQL 2005 box. Now I need to migrate it to a MYSQL system. I can't seem to get the syntax quite right.

 

SELECT ed.EntityID, ed.CustomerName, ed.IsActive, pd.ProductLine, pd.ProductVersion
FROM ENTITY_DATA ed INNER JOIN 
(SELECT row_number() over(partition by EntityID order by PollingID desc) as seq, 
ProductLine, ProductVersion, EntityID, PollingID FROM POLLING_DATA) pd 
ON ed.EntityID = pd.EntityID WHERE seq = '1' AND ed.IsActive = '1'

 

The goal of the query is to select the most recent instance from the POLLING_DATA for the specific EntityID. In the ENTITY_DATA table, a single entry is created for each customer. In the POLLING_DATA table, multiple entries are created for each customer, each entry containing the specific version of software they are running. I need to select the customer number and name from the ENTITY_DATA table and the most recent version information from the POLLING_DATA table.

[artacus]

MySQL doesn't have windowing functions so you'll have to scrap that approach.

 

But just select the entityID and max(pollingID) in a subquery and join that max pollingID to get the correct product line and version.