Skip to Main Content
  • Questions

  • Migrate an Oracle Query with LEAD function to equivalent MySql Query

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Samuel.

Asked: September 05, 2017 - 6:54 am UTC

Last updated: September 05, 2017 - 2:07 pm UTC

Version: 10.1.2

Viewed 1000+ times

You Asked

Please help me with an equivalent query in mySQL for the below:

There is a table called "proj_table" Based on cycle_id, i need to find the current and previous value for each column for a project

SELECT project_id , cycle_id ,final_value  duration,
        Lead(final_value) OVER (partition by project_id ORDER BY project_id desc, cycle_id desc) AS prev_final_value, 
        Lead(duration) OVER (partition by project_id ORDER BY project_id desc, cycle_id desc) AS prev_duration, 
        Lead(cycle_id) OVER (partition by project_id ORDER BY project_id desc, cycle_id desc) AS prev_cycle_id
        FROM proj_table

and Chris said...

Upgrade to MySQL 8.0.2 and these functions are available:

http://mysqlserverteam.com/mysql-8-0-2-introducing-window-functions/

;)

I'm not a MySQL expert, but at a guess you could do it in a similar way you would in Oracle Database. Write a scalar subquery that returns the first row

select x, (
         select tsub.x from t tsub
         where  tsub.x < t.x
         order  by tsub.x desc
         limit 1
       )
from   t;


Or you could look into this method from Quassnoi:

https://explainextended.com/2009/03/10/analytic-functions-first_value-last_value-lead-lag/

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.