Reffer To Table? - 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

Reffer To Table?

Author

Topic

aareynaga
Starting Member

6 Posts

Posted - 2009-08-15 : 16:14:37

Is there a way that i can refer to a table made from a select statement troughout my query?
I have something like this...

SELECT Table1.Column
,(Select TOP 1 Table2.Column1 FROM Table2 WHERE (blabla = blabla) AND bla bla bla bla) AS [1st]
...THEN the second nested select is the same as the first only it selects the second row

And instead of having all the calculations run twice is there any way i can set my table and call it throughout my nested selects?

Thanks.

ES QUEUE ELL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-15 : 16:22:16

You can use PIVOT.

N 56°04'39.26"
E 12°55'05.63"

aareynaga
Starting Member

6 Posts

Posted - 2009-08-15 : 16:46:54

Thanks im researching pivot but dont quite understand it...ill keep trying to figure it out though...

Im currently using this

SELECT TOP 5 Ag.City AS City
,(SELECT TOP 1 T1.Country FROM Table2 AS T1 WHERE (Ag.City = T1.SendCity) AND (T1.DateTaken >= '08/01/09') GROUP BY T1.Country ORDER BY COUNT(T1.Country) DESC) AS [1st Country]
,SELECT Table1.Country FROM (SELECT TOP 2 Rank() OVER (ORDER BY COUNT(T2.Country DESC) AS [Rank], T2.Country As [Country] FROM Table2 AS T2) AS Table1 WHERE (Table1.Rank = 2)
FROM Ag
WHERE (Ag.Status = 'A')
ORDER BY Ag.City

ES QUEUE ELL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-15 : 17:50:32

Try this one

SELECT		a.City,
		MAX(CASE WHEN t.colID = 1 THEN t.Country ELSE NULL END) AS Country1,
		MAX(CASE WHEN t.colID = 2 THEN t.Country ELSE NULL END) AS Country2
FROM		@Ag AS a
OUTER APPLY	(
			SELECT TOP 2	x.Country,
					ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS colID
			FROM		@Table2 AS x
			WHERE		x.DateTaken >= '20090801'
					AND x.SendCity = a.City
			GROUP BY	Country
			ORDER BY	COUNT(*) DESC
		) AS t
WHERE		a.[Status] = 'A'
GROUP BY	a.City
ORDER BY	a.City

N 56°04'39.26"
E 12°55'05.63"

aareynaga
Starting Member

6 Posts

Posted - 2009-08-17 : 12:58:29

MAN!.....great it worked and its alot faster than mine...thanks alot i was rattling my brain the whole weekend over this!

ES QUEUE ELL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources