ORDERING by a field in sub query

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 2005 Forums

Transact-SQL (2005)

ORDERING by a field in sub query

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-01-30 : 09:14:38

SELECT vehicleref,manufacturer,cvehicle_shortmodtext,derivative,source FROM vwAllMatrixWithLombardAndShortModel WHERE vehicleRef IN
(SELECT vehicleref FROM tblHomepageHotDeals)
GROUP BY vehicleref,manufacturer,cvehicle_shortmodtext,derivative,source

There is a field OrderBy in tblHomePageHotDeals - is there anyway I can order this query by that field

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-30 : 09:20:51

you can change the WHERE vehicleRef IN (SELECT...) to an INNER JOIN with tblHomepageHotDeals. That way columns from both tables are available for any of the clauses.


SELECT	m.vehicleref
	,m.manufacturer 
	,m.cvehicle_shortmodtext
	,m.derivative
	,m.source 
FROM	vwAllMatrixWithLombardAndShortModel  m
inner join tblHomepageHotDeals d 
		on d.vehicleRef = m.vehicleRef
GROUP BY m.vehicleref
	,m.manufacturer 
	,m.cvehicle_shortmodtext
	,m.derivative
	,m.source 
	,d.orderBy
order by d.orderBy

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources