Selecting multiple rows in subquery

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)

Selecting multiple rows in subquery

Author

Topic

paulbrill
Starting Member

3 Posts

Posted - 2009-03-12 : 15:02:48

I have a query with this sort of format:

SELECT
'1' AS one,
'2' AS two,
(SELECT TOP(1) three
FROM MyView) AS three,
(SELECT TOP(1) four
FROM MyView) AS four

The problem is that MyView takes a long time to populate, so I really only want to access it once. Unfortunately I can't do a "SELECT TOP(1) three, four" in that part of the query because you can only select one in that case.

Any thoughts on how to access this view just once?

Bodestone
Starting Member

18 Posts

Posted - 2009-03-12 : 17:32:51

it's a tad vague but can you not do
SELECT TOP 1
'1' AS [One],
'2' AS [Two],
Three,
Four
FROM MyView

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 17:38:49

TOP 1 of what?
Since there is no ORDER BY, thre returned value could be any value.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 17:39:24

SELECT
'1' AS one,
'2' AS two,
max(three) AS three,
max(four) AS four
from MyView

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

paulbrill
Starting Member

3 Posts

Posted - 2009-03-12 : 17:55:09

Well, my full query is actually about 80 lines long... I've had to wrap various subqueries to get it to work properly. I know my stuff is vague, but it's better than posting the entire thing.

I guess a better explanation of my situation is...

SELECT
column1,
column2,
(SELECT TOP(1) anotherColumn1
FROM MyView ORDER BY something1, something2, something3) AS column3,
(SELECT TOP(1) anotherColumn2
FROM MyView ORDER BY something1, something2, something3) AS column4
FROM
MyTable JOIN MyOtherTable ...

...and a whole lot of complicated stuff within that. I suppose an additional join to the view is the way I should go, although it's probably going to be just as inefficient as I do the filtering for the view.

Edit:
However, now that I think about it, on the join, I'm not going to get the filtering of something1, something2, and something3 that I want... and even then, I'd have to access MyView multiple times while filtering.

Bodestone
Starting Member

18 Posts

Posted - 2009-03-12 : 18:36:07

Requirements can be a bitch. I once had to write a report (ok so I call it a data dump) that had 18 puter joins to the same table.

One thing I might suggest is creating a view specifically for getting the max/min values from each of these columns per unique ID then joining that view. It may optimise better.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources