TOP 1 SUBQUERY - HARD!!! - 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)

TOP 1 SUBQUERY - HARD!!!

Author

Topic

ivanivan
Starting Member

1 Post

Posted - 2004-07-19 : 05:10:06

Hi there,

I am having trouble resolving the following query. I need to get the top 1 value of the following subquery. I get an error telling me that the subquery only allows 1 selwect list item.

can you help as i really need to select more than 1 item:

SELECT TOP 1 ticker, abs_single_pos_limit FROM
imported_positions WHERE
(SELECT strategy,account_no,ticker,
single_pos_limit = sum(((quantity*closing_price)/usd_rate)/15000000),
abs(single_pos_limit) as abs_single_pos_limit
FROM imported_positions
where tick_value is null
group by strategy,account_no,ticker)

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-19 : 05:40:39

Get rid of the WHERE clause - meaningless. Use the select statement as a derived table.


SELECT TOP 1 ticker, abs_single_pos_limit FROM
(SELECT strategy,account_no,ticker,
single_pos_limit = sum(((quantity*closing_price)/usd_rate)/15000000),
abs(single_pos_limit) as abs_single_pos_limit
FROM imported_positions
where tick_value is null 
group by strategy,account_no,ticker) AS MyTable

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-19 : 05:42:40

Or try this:

SELECT TOP 1 a.ticker, a.abs_single_pos_limit
FROM imported_positions a
JOIN (SELECT strategy,account_no,ticker,
abs(sum(((quantity*closing_price)/usd_rate)/15000000)) as abs_single_pos_limit
FROM imported_positions
where tick_value is null
group by strategy,account_no,ticker) as b
on a.abs_single_pos_limit = b.abs_single_pos_limit

Duane.

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-19 : 05:51:27

Why the join Duane ? Will that be faster than a derived table ?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-19 : 06:07:39

I am treating the derived table as just another Table being joined to the Imported_Positions table.

As far as efficiency is concerned - I think it might be but the only way to know for sure is to test.

Ivanivan will have to let us know.

Duane.

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-19 : 06:50:22

I get the idea of the derived table, however the derived table is based upon IMPORTED_POSITIONS tables anyway, so I don't see the point of joining the whole table to what is essentially a subset, and then selecting from that.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-19 : 09:37:58

if you're going to do doing TOP 1's you might want to throw an ORDER BY clause in there, otherwise it won't return anything meaningful. unless you want just 1 random row from your data.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources