CODEWITHSUNDEEP
sqlite
Ga Mmeeu
Ga Mmeeu

Reputation: 325

SELECT in SELECT

I have the following query which I am trying to rewrite:

SELECT 
    max(dpHigh) AS High
FROM DailyPrices
WHERE dpTicker = 'DL.AS' 
AND dpDate IN
(SELECT
    dpDate
 FROM DailyPrices
 WHERE dpTicker ='DL.AS'
 ORDER BY update DESC
 LIMIT 10);

The query gives me the required result:

bash-3.2$ sqlite3 myData < Queries/high.sql
High      
----------
4.67      
bash-3.2$

Since next to the high value I wish to expand this query to also obtain a low value, earliest date, latest date, etc. For this reason, I am trying re-write an equivalent query using a select in select statement.

SELECT 
    (SELECT         
        max(dpHigh)
    FROM DailyPrices
    WHERE dpTicker = 'DL.AS'
    AND dpDate IN 
        (SELECT dpDate
            FROM DailyPrices
            WHERE dpTicker ='DL.AS'
            ORDER BY dpDate DESC
            LIMIT 10) 
    )AS High
FROM DailyPrices
WHERE dpTicker = 'DL.AS';

Execution of the query spits output the expected value, however, it does exactly for the number of data entries of 'DL.AS'.

...
4.67      
4.67      
4.67      
4.67      
4.67      
4.67      
4.67      
bash-3.2$

Since I am a SQLite newbie, I am probably overlooking the obvious. Does anybody have any suggestions?

BR

GAM

Upvotes: 0

Views: 134

Answers (1)

CL.
CL.

Reputation: 180080

The outermost query looks like this:

SELECT (...)
FROM DailyPrices
WHERE dpTicker = 'DL.AS';

This will generate one output row for each table row with a matching dpTicker.

To generate a single row, regardless of how many rows might be found in some table, use a query without a FROM (the filtering and aggregation is already handled in the subqueries):

SELECT (...) AS High,
       (...) AS Low;

Upvotes: 1

Related Questions