Results 1 to 11 of 11

Thread: Mysql Nested sub query

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    98

    Mysql Nested sub query

    Hi All

    I need some help with a query in Mysql, which i would think that
    a nested sub query would be required.
    This is probably better understood with an example of what the output should
    look like.
    basically i need the query to run on a meter readings table and return
    the last two readings in date order descending for each asset.

    I hope that this makes senseName:  source_data_table.png
Views: 149
Size:  13.2 KBName:  output_query.png
Views: 164
Size:  6.6 KB

    I hope i have attached the pics of the source data table and query ok.

    Any assistance would be greatly appreciated

  2. #2

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    98

    Re: Mysql Nested sub query

    what i have done to improvise is to run two seperate queries, the first query selects a list of all the assets, then using programming code
    i itterate through this table and then run a second query that selects the last 2 transactions in descending order by setting the order and a limit of 2
    on the query. I then build the output of each query back into a single data table which is a long winded way of doing what i believe can be done in a single query

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Mysql Nested sub query

    On first glance this looks like a case for ROW-NUMBER() OVER(PARTITION BY asset_id ORDER BY reading_date DESC) As RN
    with a Filter in the WHERE-clause RN<=2

    Do have sample-data as a csv?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    98

    Re: Mysql Nested sub query

    Hi Zvoni,

    I created those dummy tables in excel, would that help?

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    98

    Re: Mysql Nested sub query

    Ok i have the source table example in csv, how do i upload it?
    i don't see any way of attaching a file

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Mysql Nested sub query

    ZIP it first, click on "go advanced" then on manage attachments
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    98

    Re: Mysql Nested sub query

    Quote Originally Posted by Zvoni View Post
    ZIP it first, click on "go advanced" then on manage attachments
    source_table.zip

    That is a sample of the source data.

    Thanks for your assistance

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Mysql Nested sub query

    Works in SQLite. No Idea if you have to change anything for MySQL
    SQL Code:
    1. SELECT DISTINCT
    2. T1.asset_id,
    3. T2.meter_reading,
    4. T2.reading_date
    5. FROM
    6. source_table T1
    7. INNER JOIN
    8. (SELECT
    9. asset_id,
    10. meter_reading,
    11. reading_date,
    12. ROW_NUMBER() OVER(PARTITION BY asset_id ORDER BY reading_date DESC) AS RN
    13. FROM
    14. source_table) As T2
    15. ON
    16. T1.asset_id=t2.asset_id AND
    17. T2.RN<=2
    18. ORDER BY
    19. T1.asset_id,
    20. T2.reading_date DESC
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    98

    Re: Mysql Nested sub query

    Thanks very much Zvoni
    It does not run on my db, i think that it may have something to do with the fact that this is Mysql version 5??
    I get the error 'SELECT contains sub query in the FROM clause'

  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Mysql Nested sub query

    Huh?
    Any chance to upgrade to at least 5.7 or 8?
    Otherwise:
    Create a View from the INNER SELECT_Statement (since it doesn't have parameters/dynamic filters)
    the you can address that view as any other common table

    SQL Code:
    1. CREATE VIEW vwAssetReading AS
    2. SELECT asset_id, meter_reading, reading_date, ROW_NUMBER() OVER(PARTITION BY asset_id ORDER BY reading_date DESC) AS RN
    3. FROM source_table;
    SQL Code:
    1. SELECT DISTINCT
    2. T1.asset_id,
    3. T2.meter_reading,
    4. T2.reading_date
    5. FROM
    6. source_table As T1
    7. INNER JOIN
    8. vwAssetReading As T2
    9. ON
    10. T1.asset_id=t2.asset_id AND
    11. T2.RN<=2
    12. ORDER BY
    13. T1.asset_id,
    14. T2.reading_date DESC
    Last edited by Zvoni; Mar 25th, 2021 at 07:56 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    98

    Re: Mysql Nested sub query

    Thanks mate, will try that

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width