Querying the Result set of a Previous Query

Question

I have a query for example Query1 = Select Name from table where some Criteria. Now this query returns a result set of course, what I want is to query the result set of this query, for example I only want the unique Names from the above query select Distinct(Name) from Query1. I should mention that I know I can just use distinct in Query1 but this is just an example my real scenario is somewhat different, what I want to know is whether it's possible to query the result set of a previous query. I am using SQL Server 2012.

You can put the result of your first query into a temp table and then query from there. — Felix Pamittan, Mar 31 '15 at 07:13
i kinda thought of that already but i am wondering if there is another way. thanks for the comment — Abdullah Malikyar, Mar 31 '15 at 07:17

score 4 · Accepted Answer · answered Mar 31 '15 at 07:15

4

You can use the WITH clause

WITH SomeClients AS (
    SELECT
        c.ID
    FROM Clients c
    WHERE c.Name LIKE '%hello%'
)

SELECT DISTINCT
    sc.ID
FROM SomeClients sc

answered Mar 31 '15 at 07:15

thebreiflabb

1,504
10
19

thank you, thats exactly what i was looking for i would vote you up but i am little low on reputation. – Abdullah Malikyar Mar 31 '15 at 07:23

score 4 · Answer 2 · edited May 23 '17 at 12:30

4

There are several ways to solve this:

1: create a view from the first query and run the second query on the view.

2: nest both queries, like this:

SELECT DISTINCT [Name]
FROM (
  SELECT [Name] 
  FROM table 
  WHERE some Criteria
) As InnerQuery

3: use a temporary table to store the resultset of the first query as suggested by wewesthemenace in the comments.

4: use CTE as suggested the thebreiflabb in the other answer to this post.

Personally, I would probably go with the first or second option, depending if you need to use the first query as stand alone as well.

edited May 23 '17 at 12:30

Community

1
1

answered Mar 31 '15 at 07:20

Zohar Peled

79,642
10
69
121

My Question is, `Select distinct Name from table where some Criteria` will work or not?? – A_Sk Mar 31 '15 at 07:27
The OP wrote that this is a simplified example and in his scenario he can't use distinct on the original query (my guess is that he has other columns returned as well). in this simplified example you can simply add the distinct in the first query. – Zohar Peled Mar 31 '15 at 07:30
thank you guys i think the best solution is using With clause for my scenario . – Abdullah Malikyar Mar 31 '15 at 09:34
@FaisalMalikyar no problem, as long as you are aware of the fact that there are other solutions as well :-) – Zohar Peled Mar 31 '15 at 10:05

score 0 · Answer 3 · answered Mar 31 '15 at 07:22

0

You need WITH clause. The Syntax Is-

WITH someName AS(
 //Your Db Query
)
SELECT * FROM someName // OR Whatever you want

answered Mar 31 '15 at 07:22

Vivek Mishra

1,772
1
17
37

He doesn't **need** to use a CTE. he **can** choose to use it, but there are other solutions as well. – Zohar Peled Mar 31 '15 at 07:33

score 0 · Answer 4 · answered Mar 31 '15 at 09:44

0

you can create a table to store the results temporarily and use that table in a new query

DECLARE @resultset table (
    ID   int identity(1,1) not null
  , name nvarchar(100)
)

Select top 50 application_Name
into  resultset
from Applications_ASIS

answered Mar 31 '15 at 09:44

David Defossez

11
2

Querying the Result set of a Previous Query

4 Answers4

Linked