CODEWITHSUNDEEP
sqlsql-servert-sql
user2692768
user2692768

Reputation: 61

Run a Select query based on result from other query

I have a table with 2 column with numbers column1 and column2 that are populated with a couple 1000's fields.

I first run the following code:

    select top (10) PERCENT
    col1
    from newtable

For this query I get 300 results, and based on this results I want to run a similar query as above but with my second table column

    select top (10) PERCENT
    col2
    from "based on the results in the first query"

Is this possible in some way with nested query or something similar?

Upvotes: 3

Views: 2686

Answers (2)

Martin Brown
Martin Brown

Reputation: 25310

It is possible to use a sub query if all the columns are included in the inner query like this:

SELECT TOP(10) PERCENT col2
FROM 
    (
        SELECT TOP(10) PERCENT col1, col2
        FROM SomeTable
    ) AS TopTenPercent

I'm not sure this would be a very useful query as it does not give any guarantee as to which rows you are going to get. This is because SQL Server could arrange the rows in the table in any way it sees fit when optimizing the query. As such this will really only give you a selection of col2 for 1% of the rows.

If you want to get the top ten percent of values ordered in some way it would require an ORDER BY. So to get the top 10% of column two values ordered ascending from the rows that have the top 10% of column one values ordered descending then you would use this:

SELECT TOP(10) PERCENT col2
FROM 
    (
        SELECT TOP(10) PERCENT col1, col2
        FROM Scrap
        ORDER BY col1 DESC
    ) AS TopTenPercent
ORDER BY col2 ASC

Upvotes: 2

Isaac
Isaac

Reputation: 3363

Without more details and sample data I cannot say for sure if this will work for you, but it seems like you want something like this.

SELECT TOP (10) PERCENT
       col2
FROM   newtable
WHERE  col1 IN (
                   SELECT TOP (10) PERCENT col1 FROM newtable
               );

Upvotes: 3

Related Questions