-
Mar 25th, 2021, 01:48 AM
#1
Thread Starter
Lively Member
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 sense
I hope i have attached the pics of the source data table and query ok.
Any assistance would be greatly appreciated
-
Mar 25th, 2021, 01:54 AM
#2
Thread Starter
Lively Member
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
-
Mar 25th, 2021, 02:07 AM
#3
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
-
Mar 25th, 2021, 02:12 AM
#4
Thread Starter
Lively Member
Re: Mysql Nested sub query
Hi Zvoni,
I created those dummy tables in excel, would that help?
-
Mar 25th, 2021, 02:16 AM
#5
Thread Starter
Lively Member
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
-
Mar 25th, 2021, 03:19 AM
#6
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
-
Mar 25th, 2021, 03:47 AM
#7
Thread Starter
Lively Member
Re: Mysql Nested sub query
Originally Posted by Zvoni
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
-
Mar 25th, 2021, 06:40 AM
#8
Re: Mysql Nested sub query
Works in SQLite. No Idea if you have to change anything for MySQL
SQL Code:
SELECT DISTINCT T1.asset_id, T2.meter_reading, T2.reading_date FROM source_table T1 INNER JOIN (SELECT asset_id, meter_reading, reading_date, ROW_NUMBER() OVER(PARTITION BY asset_id ORDER BY reading_date DESC) AS RN FROM source_table) As T2 ON T1.asset_id=t2.asset_id AND T2.RN<=2 ORDER BY T1.asset_id, 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
-
Mar 25th, 2021, 07:28 AM
#9
Thread Starter
Lively Member
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'
-
Mar 25th, 2021, 07:50 AM
#10
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:
CREATE VIEW vwAssetReading AS SELECT asset_id, meter_reading, reading_date, ROW_NUMBER() OVER(PARTITION BY asset_id ORDER BY reading_date DESC) AS RN FROM source_table;
SQL Code:
SELECT DISTINCT T1.asset_id, T2.meter_reading, T2.reading_date FROM source_table As T1 INNER JOIN vwAssetReading As T2 ON T1.asset_id=t2.asset_id AND T2.RN<=2 ORDER BY T1.asset_id, 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
-
Mar 25th, 2021, 11:57 AM
#11
Thread Starter
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|