Show Only Most Recent Date from Joined MySQL Table

Show only most recent date from joined MySQL table

This can be done with a subquery:

SELECT d.docID, docTitle, c.dateAdded, c.content
FROM document d LEFT JOIN content c ON c.docID = d.docID
WHERE dateAdded IS NULL
    OR dateAdded = (
        SELECT MAX(dateAdded)
        FROM content c2
        WHERE c2.docID = d.docID
    )

This is known as a "groupwise maximum" query

Edit: Made the query return all document rows, with NULLs if there is no related content.

Join tables based on most recent date for each record

An OUTER APPLY is like the LEFT JOIN that you tried, but it allows you to join to an inline view AND it allows you to refer to columns from previously joined tables in the WHERE clause of that inline view.

Using OUTER APPLY you can use the WHERE clause to find all the backups that occurred on or before each date, use the ORDER BY clause to sort them by backup date latest-to-earliest, and then use the FETCH FIRST clause to just get the first one in the sorted list (i.e., the latest backup).

SELECT d.*, b.DATE most_recent_backup
FROM   my_dates d
OUTER APPLY ( SELECT b.date
              FROM backups b 
              WHERE b.date <= d.date 
              ORDER BY b.date DESC
              FETCH FIRST 1 ROW ONLY ) b

You can also do this with NOT EXISTS if you aren't on a version of Oracle that supports OUTER APPLY. Something like this:

SELECT d.*, b.DATE most_recent_backup
FROM   my_dates d
LEFT JOIN backups b ON b.date <= d.date
WHERE ( 
    -- Either the backup date is NULL (this happens if the LEFT JOIN
    -- found no backups earlier than the given date)
    b.date IS NULL
    OR  
    -- Or there is a backup later than the row backup we are looking at.
    -- The LEFT JOIN condition joins each date to ALL the backups that
    -- happened on or before that date.  This condition excludes
    -- every backup for a given date except for the most recent one.
    -- If the backup is not the most recent backup on or before a 
    -- given date, there will exist a later backup that is also on
    -- or before that same date.
    NOT EXISTS ( SELECT 'later backup that is earlier than date'
                     FROM   backups b2
                     WHERE  b2.date <= d.date
                     AND    b2.date > b.date )
      )

Selecting Most Recent Date Relative to Another Table

Join twice the tables, once to get the min date difference and then to get the row with the min date difference:

select a.*
from tablea a 
inner join tableb b on b.name = a.name 
inner join (
  select a.name, min(abs(datediff(b.date, a.date))) mindatediff
  from tablea a inner join tableb b
  on b.name = a.name
  group by a.name           
) g on g.name = a.name and abs(datediff(b.date, a.date)) = g.mindatediff

See the demo.

or:

select a.*
from tablea a inner join tableb b
on b.name = a.name
where abs(datediff(b.date, a.date)) = (
    select min(abs(datediff(x.date, y.date))) 
    from tablea x inner join tableb y
    where x.name = a.name and y.name = b.name                            
)

See the demo.

Results:

| date       | name | price |
| ---------- | ---- | ----- |
| 2019-03-17 | Bob  | 8     |
| 2019-03-20 | John | 10    |

Select row with most recent date per user

Query:

SQLFIDDLEExample

SELECT t1.*
FROM lms_attendance t1
WHERE t1.time = (SELECT MAX(t2.time)
                 FROM lms_attendance t2
                 WHERE t2.user = t1.user)

Result:

| ID | USER |       TIME |  IO |
--------------------------------
|  2 |    9 | 1370931664 | out |
|  3 |    6 | 1370932128 | out |
|  5 |   12 | 1370933037 |  in |

Note that if a user has multiple records with the same "maximum" time, the query above will return more than one record. If you only want 1 record per user, use the query below:

SQLFIDDLEExample

SELECT t1.*
FROM lms_attendance t1
WHERE t1.id = (SELECT t2.id
                 FROM lms_attendance t2
                 WHERE t2.user = t1.user            
                 ORDER BY t2.id DESC
                 LIMIT 1)

MySQL - Select the most recent record by date from two tables

SELECT p.GTIN, p.Name, p.Dosage, P.Quantity, pp.product_price
FROM Product p INNER JOIN
     Product_price pp
     ON p.GTIN = pp.GTIN
WHERE pp.date = (SELECT MAX(p2.date)
                FROM Product_price p2
                WHERE p2.GTIN = pp.GTIN
               );

Get the most recent record by date in mysql

Here are your queries, but first!!! It is never a good idea to use SQL keywords as column names. If you do that you must quote tzhe fieldname with backticks.

get one result

SELECT 
CODE, price, DATE 
FROM 
prices 
WHERE 
`date`= (SELECT MAX(DATE) FROM prices);

get all results

SELECT p1.`code`, p1.`maxdate`, p2.`price` FROM (
    SELECT `CODE`, MAX(`DATE`) AS maxdate FROM prices GROUP BY `CODE`) AS p1
LEFT JOIN prices p2 ON p1.`code`= p2.`code`AND p2.`date` = p1.maxdate
ORDER BY p1.`code`;

sample

MariaDB [order]> SELECT 
    -> CODE, price, DATE 
    -> FROM 
    -> prices 
    -> WHERE 
    -> `date`= (SELECT MAX(DATE) FROM prices);
+-----------+-------+------------+
| CODE      | price | DATE       |
+-----------+-------+------------+
| ADV000001 |    20 | 2021-11-23 |
+-----------+-------+------------+
1 row in set (0.06 sec)

MariaDB [order]> SELECT p1.`code`, p1.`maxdate`, p2.`price` FROM (
    -> SELECT `CODE`, MAX(`DATE`) AS maxdate FROM prices GROUP BY `CODE`) AS p1
    -> LEFT JOIN prices p2 ON p1.`code`= p2.`code`AND p2.`date` = p1.maxdate
    -> ORDER BY p1.`code`;
+-----------+------------+---------+
| code      | maxdate    | price   |
+-----------+------------+---------+
| ADV000001 | 2021-11-23 |      20 |
| ADV000112 | 1993-06-11 |  685.04 |
| ADV000123 | 2017-04-21 |    31.4 |
| ADV000202 | 2011-11-11 |  511.99 |
| ADV000215 | 2011-12-11 |  856.81 |
| ADV000777 | 2012-04-11 | 1352.12 |
| ADV000912 | 2021-01-11 |   111.1 |
| ADV000991 | 2021-04-09 |  242.66 |
| ADV011912 | 1994-11-11 | 1324.11 |
| ADV031242 | 2019-07-11 |  231.42 |
+-----------+------------+---------+
10 rows in set (0.01 sec)

MariaDB [order]> 

MySQL: Join on most recent date

This query will give you the result you want. It JOINs table_2 to table_1 on table_1 having the maximum dt less than or equal to the table_2 dt value:

SELECT t2.dt, t1.x
FROM table_2 t2
JOIN table_1 t1 ON t1.dt = (SELECT MAX(dt) FROM table_1 WHERE table_1.dt <= t2.dt)

Output:

dt          x
2018-01-01  1
2018-01-03  1
2018-01-04  1
2018-01-06  4
2018-01-08  2
2018-01-09  2
2018-01-11  2

To create your my_joined_table table, just use a CREATE TABLE ... SELECT query:

CREATE TABLE my_joined_table AS
SELECT t2.dt, t1.x
FROM table_2 t2
JOIN table_1 t1 ON t1.dt = (SELECT MAX(dt) FROM table_1 WHERE table_1.dt <= t2.dt)

Demo on dbfiddle

Sql join to table based on most recent date and time

I used row_number in case you have more than one driver and you want the results from all of them in the same table.

I used SQL Server instead of DB2, but it's pretty simple so it should translate well.

  select  driver_num 
       ,name
       ,destination 
       ,arrived_date    
       ,arrive_time
from   (
        select routes.driver_num 
              ,routes.destination   
              ,routes.arrived_date  
              ,routes.arrive_time
              ,drivers.name
              ,row_number() over(partition by drivers.driver_num order by routes.arrived_date desc, routes.arrive_time desc) as rn
        from drivers join routes on routes.driver_num = drivers.driver_num
        where  drivers.home <> routes.destination
        ) t
where rn = 1
 
 
 

---