How to select all values for stock, which match certain condition

29 Sep 2005 3:46 PM

John

Hi,

I need to make a query on a single table containing historical stock data,
something like:
ID, StockID, Date, Open, Close, Volume, ...

The problem is that I need to get All records for the stock,
for which at least one record matches some condition.

An obvious solution is:

select * from StockValues where StockID IN
(select DISTINCT StockID from StockValues where XXX)
AND YYY

XXX is the condition.
YYY is an optional additional condition for records, such as date range

Question: what is the most efficient way to perform such a query?
Is it possible to do it without the subquery?

Thank you
John

PS: I am sure this question was already discussed many times, but I cannot
find any,
so I am posting it again, sorry.

29 Sep 2005 3:53 PM

Jerry Spivey

John,

The DDL and INSERTs (sample data) would make this easier. That said - why
don't you use WHERE STOCKID = ...without the subquery?

If you can't and you do need a subquery, try EXISTS instead of IN. Using
EXISTS can be much faster.

HTH

Jerry
Show quote

<John> wrote in message news:_rmdnVICOfb2kaHeRVn-uQ@speakeasy.net...
> Hi,
>
> I need to make a query on a single table containing historical stock data,
> something like:
> ID, StockID, Date, Open, Close, Volume, ...
>
> The problem is that I need to get All records for the stock,
> for which at least one record matches some condition.
>
> An obvious solution is:
>
> select * from StockValues where StockID IN
> (select DISTINCT StockID from StockValues where XXX)
> AND YYY
>
> XXX is the condition.
> YYY is an optional additional condition for records, such as date range
>
> Question: what is the most efficient way to perform such a query?
> Is it possible to do it without the subquery?
>
> Thank you
> John
>
> PS: I am sure this question was already discussed many times, but I cannot
> find any,
> so I am posting it again, sorry.
>

30 Sep 2005 5:11 AM

John

Hi,
Thank you for the answers.

> That said - why don't you use WHERE STOCKID = ...without the subquery?

Because the purpose of the query is to find stocks, which match a condition
and get their values.

> Using EXISTS can be much faster.

I tried. No difference whatsoever (at least in my sample queries).

Thank you
John

29 Sep 2005 4:07 PM

David Portas

Use EXISTS for example:

SELECT ...
FROM StockValues AS S
WHERE EXISTS
(SELECT *
   FROM StockValues
   WHERE ... ?
     AND stockid = S.stockid)

--
David Portas
SQL Server MVP
--