Ruby-on-Rails [SOLVED]: Select last column record with group_by

Ruby-on-Rails [SOLVED]: Select last column record with group_by

Home Forums Frameworks Ruby on Rails Tutorials Ruby-on-Rails [SOLVED]: Select last column record with group_by

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #246643

    Cloudy Point
    Keymaster

    QuestionQuestion

    I have a table like this:

    ----------------------------------
    | post | page_likes | created_at |
    ----------------------------------
    |   2  |     700    | 2017-11-02 |
    ----------------------------------
    |   1  |     702    | 2017-11-03 |
    ----------------------------------
    |   4  |     709    | 2017-11-04 |
    ----------------------------------
    |   2  |     712    | 2017-11-05 |
    ----------------------------------
    |   0  |     710    | 2017-11-06 |
    

    And a query to calculate data and use it for my chart

    SELECT DATE_FORMAT(created_at, '%Y%m') as ymd_id,
                      page_likes AS page_likes,
                      sum(post) AS total_posts
    
                      FROM table
                        Where created_at BETWEEN '2017-11-02 00:00:00' AND '2017-11-6 00:00:00'
                      GROUP BY ymd_id
                      ORDER BY ymd_id ASC
    

    Result:

    ---------------------------------------
    |total_posts| page_likes |   ymd_id   |
    ---------------------------------------
    |     9     |     700    |   201711   |
    ---------------------------------------
    

    page_like goes wrong. It takes 700, but the value i want is 710.

    Is there a way to modify the query above to get last row of page_like?

    #246644

    Cloudy Point
    Keymaster

    Accepted AnswerAnswer

    Another approach using string function

    SELECT 
      DATE_FORMAT(created_at, '%Y%m') AS ymd_id,
      SUBSTRING_INDEX(GROUP_CONCAT(page_likes ORDER BY created_at DESC),',',1) AS page_likes,
      SUM(post) AS total_posts 
    FROM
      demo 
    WHERE created_at >= '2017-11-02 00:00:00' 
      AND created_at <= '2017-11-6 00:00:00' 
    GROUP BY ymd_id 
    ORDER BY ymd_id ASC 
    

    DEMO

    Source: https://stackoverflow.com/questions/47898233/select-last-column-record-with-group-by
    Author: M Khalid Junaid
    Creative Commons License
    This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.