Hi,
i have two tables , the first one look like this Col1 Col2 Col3 1 aaaa bbbb 2 yyyy zzzz 3 uuuu www
second table FK PK RevNr 1 1 1 2 2 1 2 3 2 2 4 3 3 5 1
Col1 is the primary key in table one, and in table two the primaray key is PK and FK is the foreign key to table one.
I need a select wich return me all rows table one and from table two only the rows with the max(revision)
So the result should look like this: Col1 Col2 Col3 FK PK RevNr
1 aaaa bbbb 1 1 1 2 yyyy zzzz 2 4 3 ( only the row with the greatest RevNr is returned )
3 uuuu www 3 5 1
SELECT *
FROM table1
INNER JOIN table2 ON table1.Col1=table2.FK;
You need to know what join to use: INNER JOIN, LEFT OUTER JOIN or RIGHT OUTER JOIN but try to use INNER JOIN and LEFT OUTER JOIN only as most people find the query harder to read when you use RIGHT OUTER JOIN. Use google/youtube for examples.
When that works you can aggregate with for example SUM(), MAX() or MIN(). Each column not used as an aggregate should be in the GROUP BY.
SELECT table1.Col1, table1.Col2, table1.Col3, table2.Fk, table2.PK, MAX(RevNr) AS RevNr
FROM table1
INNER JOIN table2 ON table1.Col1=table2.FK
GROUP BY table1.Col1, table1.Col2, table1.Col3, table2.Fk, table2.PK;
CREATE TABLE #t1
(
col1 int NOT NULL PRIMARY KEY
,col2 varchar(10) NOT NULL
,col3 varchar(10) NOT NULL
);
INSERT INTO #t1
VALUES (1, 'aaaa', 'bbbb')
,(2, 'yyyy', 'zzzz')
,(3, 'uuuu', 'www');
CREATE TABLE #t2
(
FK int NOT NULL
,PK int NOT NULL PRIMARY KEY
,RevNr int NOT NULL
);
INSERT INTO #t2
VALUES (1, 1, 1)
,(2, 2, 1)
,(2, 3, 2)
,(2, 4, 3)
,(3, 5, 1);
A common method:
WITH RevOrd
AS
(
SELECT FK, PK, RevNr
,ROW_NUMBER() OVER (PARTITION BY FK ORDER BY RevNr DESC) AS rn
FROM #t2
)
SELECT T.col1, T.col2, T.col3
,R.FK, R.PK, R.RevNr
FROM #t1 T
JOIN RevOrd R
ON T.Col1 = R.FK
AND R.rn = 1;