SQLTeam.com | Weblogs | Forums

Select over two tables group by

Transact-SQL
#1

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

Thanks for any suggestion

#2

Welcome drentsch,

First you need to join the 2 tables:

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;

Hopes it works!

#3

Please post consumable test data in future:

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;
#4

Hi Ifor,

next time i will provide tables as you said.
Your example works perfect, it's exactly what i need.

Thanks and regards