need a set based solution - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

need a set based solution

Author

Topic

marat
Yak Posting Veteran

85 Posts

Posted - 2008-07-07 : 20:37:28

Hi,
I have 2 tables A and B.
I need for each row in table A to find top 1 row in table B where some contitions apply.
I need a set based solution.
Thank you

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-08 : 04:24:37

Something like...


DECLARE @tablea TABLE (
	employeeID INT
	, [name] VARCHAR(50)
	)

DECLARE @tableb TABLE (
	[Id] INT IDENTITY(1,1)
	, [employeeID] INT
	, [startDate] DATETIME
	, [value] VARCHAR(50)
	)

INSERT INTO @tablea VALUES (10, 'Mr Green')
INSERT INTO @tablea VALUES (11, 'Mr Black')

INSERT INTO @tableb VALUES (10, '2008-01-01', 'Gangster Training A')
INSERT INTO @tableb VALUES (10, '2008-01-02', 'Advanced GT B')
INSERT INTO @tableb VALUES (11, '2008-01-01', 'Goon Training A')
INSERT INTO @tableb VALUES (11, '2008-02-02', 'Adv Goon Training A')
INSERT INTO @tableb VALUES (11, '2008-06-01', 'Burial - Goon Hall B')

SELECT
	a.[name]
	, b.[startDate]
	, b.[value]
FROM
	@tablea a

	JOIN (
		SELECT
			b.[employeeId] AS empId
			, MAX(b.[Id]) AS tablebId
		FROM
			@tableb b
		GROUP BY
			b.[employeeId]
	)
	hTableb ON hTableb.[empId] = a.[employeeId]

	JOIN @tableb b ON b.[Id] = hTableb.[tablebId]

Which will bring back the latest thing (by ID) to happen to each of the people in tablea.

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-08 : 04:28:28

And if you wanted to find out the latest thing that happened in January to each...


DECLARE @tablea TABLE (
	employeeID INT
	, [name] VARCHAR(50)
	)

DECLARE @tableb TABLE (
	[Id] INT IDENTITY(1,1)
	, [employeeID] INT
	, [startDate] DATETIME
	, [value] VARCHAR(50)
	)

INSERT INTO @tablea VALUES (10, 'Mr Green')
INSERT INTO @tablea VALUES (11, 'Mr Black')

INSERT INTO @tableb VALUES (10, '2008-01-01', 'Gangster Training A')
INSERT INTO @tableb VALUES (10, '2008-01-02', 'Advanced GT B')
INSERT INTO @tableb VALUES (11, '2008-01-01', 'Goon Training A')
INSERT INTO @tableb VALUES (11, '2008-02-02', 'Adv Goon Training A')
INSERT INTO @tableb VALUES (11, '2008-06-01', 'Burial - Goon Hall B')

SELECT
	a.[name]
	, b.[startDate]
	, b.[value]
FROM
	@tablea a

	JOIN (
		SELECT
			b.[employeeId] AS empId
			, MAX(b.[Id]) AS tablebId
		FROM
			@tableb b
		WHERE
			DATEDIFF(MONTH, b.[startDate], '2008-01-01') = 0
		GROUP BY
			b.[employeeId]
	)
	hTableb ON hTableb.[empId] = a.[employeeId]

	JOIN @tableb b ON b.[Id] = hTableb.[tablebId]

That kind of thing.

-------------
Charlie

marat
Yak Posting Veteran

85 Posts

Posted - 2008-07-08 : 05:50:17

Thanks Charlie,
Problem resolved.
Key things are ORDER BY and column [Id] INT IDENTITY(1,1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources