Home > Archive > SQL Server Programming > September 2005 > How to select all values for stock, which match certain condition
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How to select all values for stock, which match certain condition
|
|
|
| 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.
| |
| Jerry Spivey 2005-09-29, 7:02 pm |
| 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
<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.
>
| |
| David Portas 2005-09-29, 7:02 pm |
| Use EXISTS for example:
SELECT ...
FROM StockValues AS S
WHERE EXISTS
(SELECT *
FROM StockValues
WHERE ... ?
AND stockid = S.stockid)
--
David Portas
SQL Server MVP
--
| |
|
| 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
|
|
|
|
|