Timeline for Limiting join to top 1 row for each row
Current License: CC BY-SA 4.0
10 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Dec 10, 2020 at 23:30 | history | edited | Rick James |
edited tags
|
|
| Nov 13, 2020 at 14:19 | answer | added | user212533 | timeline score: 1 | |
| Nov 13, 2020 at 6:48 | comment | added | dstr | @bbaird: with most recent I mean tableb.row with greatest date which is equal or smaller than current tablea.row's date. | |
| Nov 12, 2020 at 22:32 | comment | added | user212533 |
There's no answer to this because your choice of "most recent" isn't consistent across rows. If we assume we're taking the nearest row active on or after, then id = 11 from Table A should map to id = 99 from Table B, not 66. So by "most recent do you mean: 1. minimum date greater than or equal to or 2. Date separated by the fewest days?
|
|
| Nov 12, 2020 at 16:47 | answer | added | NikitaSerbskiy | timeline score: 0 | |
| Nov 12, 2020 at 13:09 | comment | added | nbk | i thought about joining a and bby month and year, but that is not possible because targetid 456 has different month.and the target id allne i can't take, that is not unique sqlfiddle.com/#!4/543f0/30 | |
| Nov 12, 2020 at 12:56 | comment | added | Akina |
using targetid as FK targetid is not unique in table b - what date must be taken for a.start_date <= b.start_date condition?
|
|
| Nov 12, 2020 at 12:31 | comment | added | Michael Kutz |
Please post the full SELECT that you tried. A table of expected results would also be nice.
|
|
| Nov 12, 2020 at 12:21 | review | First posts | |||
| Nov 12, 2020 at 13:46 | |||||
| Nov 12, 2020 at 12:18 | history | asked | dstr | CC BY-SA 4.0 |