Trying to join the nearest table row to another table
I have 2 tables and I want to join the highest, but not above the row version of table b, into table a
Table a:
REVISION_NO INFO1
1 a
2 b
3 c
4 d
5 e
6 f
Table b:
REVISION_NO INFO2
1 x
4 y
6 z
I want to:
a x
b x
c x
d y
e y
f z
Does anyone know how to do this? If it matters the database is Firebird
+3
source to share
2 answers
Here's a working solution for SQL Server 2008.
BEGIN
DECLARE @a TABLE(REVISION_NO INT, INFO1 CHAR(1))
DECLARE @b TABLE(REVISION_NO INT, INFO2 CHAR(1))
INSERT INTO @a VALUES
(1, 'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'e')
INSERT INTO @b VALUES
(1,'x'),(4,'y'),(6,'z')
--
SELECT a.INFO1
, ISNULL(b.INFO2, (
SELECT INFO2 FROM @b WHERE REVISION_NO = (
SELECT MAX(REVISION_NO)
FROM @b
WHERE REVISION_NO < a.REVISION_NO
))) [INFO2]
FROM @a a
LEFT JOIN @b b
ON b.REVISION_NO = a.REVISION_NO
END
Don't know if firebird will allow such following requests
0
source to share