CODEWITHSUNDEEP
mysqlsqlsqlite
B.A.
B.A.

Reputation: 181

SQL SELECT QUERY - from two tables

I faced a difficult problem about SQL query. Please help me

I have two tables like below.

TABLE_A                   TABLE_B
Date      Value_A         Date      Value_B
20180201   52             20180131   120
20180202   50             20180201   114
20180205   48             20180203   127
20180206   57             20180204   140
20180207   60             20180206   129

And I want to get this result.

Date       Value_A     PreValue_B
20180201    52          120
20180202    50          114
20180205    48          140
20180206    57          140
20180207    60          129

Date and Value_A are the same as TABLE_A. PreValue_B is from Value_B. But it is the value of the maximum (closest) & previous date of TABLE_B.

so, the closest previous date of 20180201 of TABLE_B is "20180131". ...

the closest previous date of 20180205 is "20180204", so PreValue_B is 140.

20180206 is "20180204", so PreValue_B is 140 again. and so on...

How to make the SQL Query?

Thanks to everyone!

Upvotes: 1

Views: 103

Answers (2)

kjmerf
kjmerf

Reputation: 4335

Try this:

SELECT sub.date, sub.a, b.b
FROM
(SELECT a.date, a.a, MAX(b.date) AS b_date
 FROM a
 INNER JOIN b
 ON (a.date > b.date)
 GROUP BY a.date, a.a) sub
INNER JOIN b
ON sub.b_date = b.date
ORDER BY sub.date

In the sub-query, find the date that should be selected in b for each date in a. Then join the results back to b, in order to show the b value.

Tested here: http://rextester.com/ERP28040

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

A typical approach uses correlated subqueries:

select a.*,
       (select b.value
        from b
        where b.date < a.date
        order by b.date desc
        fetch first 1 row only
       ) as prevalue_b
from a;

This uses the ANSI standard method for limiting to one row. Some databases spell this as limit or select top 1.

Upvotes: 2

Related Questions