#1
  1. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,464
    Rep Power
    70

    LAST_VALUE() and PARTITION BY


    Well, I've even tried to reference basic numeric columns just to rule out date type columns and such with no luck. This always fails with a syntax error. When I add an alias to the end of the line, I get a second error noting that another alias for such has already been found, but I am not seeing it. The intent of this is to grab all items from the past month, and from that set, grab all single account invoices and only grab the most recent invoices from instances where there were multiple from the same account that month.
    Code:
    SELECT `id`, `account`, `date`, `date_type`, `value`, `adj_value`, `comment`, `paid`, LAST_VALUE(`date`) OVER (PARTITION BY `account` ORDER BY `date`) FROM `invoice` WHERE YEAR(`date`) = '2017';
    The line of issue, for both errors: (Alias added that causes that error. Even tried w/ ASC/DESC for the ORDER BY)
    Code:
    LAST_VALUE(`date`) OVER (PARTITION BY `account` ORDER BY `date`) AS `last_date`
    Thanks for any assistance.

    EDIT: Referencing Analytic functions in MySQL: FIRST_VALUE, LAST_VALUE, LEAD, LAG at EXPLAIN EXTENDED
    Last edited by Triple_Nothing; July 5th, 2017 at 11:48 AM.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    87
    Rep Power
    5
    The last_value()/OVER... are not MySQL features yet (see version 8).

    The article you linked to is using a big long alias named - `LAST_VALUE(month) OVER (PARTITION BY season ORDER BY id)` for the value from the mysql user variable @r. The rest of the query shown is needed to produce that value.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,700
    Rep Power
    4288
    Code:
    SELECT invoice.id
         , invoice.account
         , invoice.`date`
         , invoice.date_type
         , invoice.value
         , invoice.adj_value
         , invoice.comment
         , invoice.paid
         , subquery.last_date
      FROM invoice 
    INNER
      JOIN ( SELECT account
                  , MAX(`date`) AS last_date
               FROM invoice
             GROUP
                 BY account ) AS subquery
        ON subquery.account = invoice.account               
     WHERE invoice.`date` >= '2017-01-01'
       AND invoice.`date`  < '2018-01-01'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,464
    Rep Power
    70
    Hmmm... Thanks for the note and assist.

    The issue with the query provided is every single row is still returned, but with the added column of 'last_date' holding the valid last date of that account. Was everything intended to be returned, or only those rows of each account that matched those dates?

    EDIT: Perhaps I should try a thing or two before responding... ^_^ A simple alteration completes my true intention. I guess the year won't matter in the fact I'm requesting the MAX() to build a new/current month item as that month comes to exist...

    Altered code works just fine. Thank you very much!
    Code:
    SELECT invoice.id
         , invoice.account
         , invoice.`date`
         , invoice.date_type
         , invoice.value
         , invoice.adj_value
         , invoice.comment
         , invoice.paid
      FROM invoice 
    INNER
      JOIN ( SELECT account
                  , MAX(`date`) AS last_date
               FROM invoice
             GROUP
                 BY account ) AS subquery
        ON subquery.account = invoice.account               
     WHERE invoice.`date` = subquery.last_date;
    Last edited by Triple_Nothing; July 5th, 2017 at 02:39 PM.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,700
    Rep Power
    4288
    Originally Posted by Triple_Nothing
    Was everything intended to be returned, or only those rows of each account that matched those dates?
    you can have it either way

    i just gave you the equivalent of what you posted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo