I stated in the above comments that this seems to be a case of a supertype-subtype relationship but, since changing your database structure may be a decision that lies outside of the scope of this question, I am going to focus on offering a solution for your current situation. ( SELECT device_id, stats_time, status, '' AS signal, noise ( SELECT device_id, stats_time, status, signal, '' AS noise Step 2: SELECT device_id, stats_time, status, signal, noise I will assume that is really the case, hence UNION: Your example does not show a case where both signal and noise exist for the same device_id. Manually run them to see if I got them right. This may be beneficial to performance: INDEX(device_id, stats_time)ĭitto for TableB and signal. ORDER BY device_id DESC, - The 'GROUP BY' Step 1 is a variant of groupwise max: SELECT device_id, stats_time, status, noise - The desired columnsĭevice_id != AS first, - `device_id` is the 'GROUP := device_id, - the 'GROUP BY'ĭevice_id, stats_time, status, noise - Also the desired columns Build tables with just the latest signal (or noise) for each device.
RIGHT JOIN devices ON A.device_id = devices.id Using the below query is not good since i get two columns of stats_time SELECT devices.id AS id, A.stats_time, B.stats_timeīefore i ended up using different tables for the device types i used to get the results with the following but ended up going real slow SELECT * | id | device_id | stats_time | status | signal | noise | I've been busting my head for a query to end up with something like this +-+-+-+-+-+-+ | id | device_id | stats_time | status | signal | | id | device_id | stats_time | status | noise | +-+-+-+Īccording to the type they have some data in different tables
Now i have a main table with the devices info. Later on i will go into partitioning but that is another issue. I'm kind of concerned about speed too since tables are growing kinda fast. #start select orders.* from orders, (select name,max(order_date) as order_date from orders group by name) max_sales where orders.name=max_sales.name and orders.order_date=max_sales.I want to JOIN two tables and get the latest result from each one of those two in a single table. #start select name,max(order_date) from orders group by name #endĪs we now know the most recent date for each group of records, we can join this data with our original table so that we can get the most recent record for each group of records. In the first step, we are going to use GROUP BY to get the most recent date for each group. In our example, let’s say that you have a table orders (name, order_date, amount) that contains sales data for a number of products at the same time.ġ.Create Table #start create table orders(name varchar(255),order_date date, amount int) #endĢ.Insert Data #start insert into orders(name,order_date, amount) values('Aa','',2500), ('Bb','',3500), ('Cb','',12500), ('Aa','',4500), ('Bb','',6500), ('Cc','',10500), ('Aa','',1500), ('Bb','',2500), ('Cc','',18500) #endįor example, let’s say that you want to get the last record for each group, that is, for each product.
#MYSQL JOIN LATEST RECORD HOW TO#
The following steps will show you how to get the last record in each group in MySQL. It can also be used to select the last row for each group in PostgreSQL, SQL Server, and Oracle databases. The following SQL query will retrieve the last record in each group in MySQL because there is no built-in function to do this in MySQL. In some cases it may be necessary to select the most recent record or get the most recent record for a particular date, user, ID or any other group. How To Get Last Record In Each Group In MySQL
0 kommentar(er)
