TOP 1 INNER JOIN - 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

General SQL Server Forums

New to SQL Server Programming

TOP 1 INNER JOIN

Author

Topic

hansen
Starting Member

2 Posts

Posted - 2006-08-18 : 04:31:30

Hi,

Is it posible to use TOP 1 on the table HRMVirtualNetworkHistory?

SELECT dbo.Empltable.EmplId, dbo.HRMVirtualNetworkHistory.hrmOrganizationId, dbo.HRMOrganization.description
FROM dbo.EMPLTABLE
INNER JOIN
dbo.HRMVirtualNetworkTable
ON
dbo.emplTable.EmplId = dbo.HRMVirtualNetworkTable.reference
INNER JOIN
dbo.HRMVirtualNetworkHistory
ON
dbo.HRMVirtualNetworkTable.hrmVirtualNetworkId = dbo.HRMVirtualNetworkHistory.hrmVirtualNetworkId
LEFT OUTER JOIN
dbo.HRMOrganization
ON dbo.HRMOrganization.hrmOrganizationId = dbo.HRMVirtualNetworkHistory.hrmOrganizationId

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-18 : 04:40:50

Yes, if you make it a derived table.

SELECT		dbo.Empltable.EmplId,
		dbo.HRMVirtualNetworkHistory.hrmOrganizationId,
		dbo.HRMOrganization.description
FROM		dbo.EMPLTABLE
INNER JOIN	dbo.HRMVirtualNetworkTable ON dbo.emplTable.EmplId = dbo.HRMVirtualNetworkTable.reference
INNER JOIN	(
			SELECT TOP 1	hrmVirtualNetworkId
			FROM		dbo.HRMVirtualNetworkHistory
			ORDER BY	SomeColumn
		) derivedtable ON derivedtable.hrmVirtualNetworkId = dbo.HRMVirtualNetworkTable.hrmVirtualNetworkId 
LEFT JOIN	dbo.HRMOrganization ON dbo.HRMOrganization.hrmOrganizationId = dbo.HRMVirtualNetworkHistory.hrmOrganizationId

Peter Larsson
Helsingborg, Sweden