CODEWITHSUNDEEP
sql-servert-sql
user594166
user594166

Reputation:

Multiple Select against one CTE

I have a CTE query filtering a table Student

  Student

   (
    StudentId PK,
    FirstName ,
    LastName,
    GenderId,
    ExperienceId,
    NationalityId,
    CityId
  )

Based on a lot filters (multiple cities, gender, multiple experiences (1, 2, 3), multiple nationalites), I create a CTE by using dynamic sql and joining the student table with a user defined tables (CityTable, NationalityTable,...)

After that I have to retrieve the count of student by each filter like

CityId City Count

NationalityId Nationality Count

Same thing the other filter.

Can I do something like

  ;With CTE(
         Select
         FROM Student
         Inner JOIN ...
         INNER JOIN ....)
  SELECT CityId,City,Count(studentId)
  FROm CTE
  GROUP BY CityId,City

  SELECT GenderId,Gender,Count
  FROM CTE
  GROUP BY  GenderId,Gender

I want to something like what LinkedIn is doing with search(people search,job search)

http://www.linkedin.com/search/fpsearch?type=people&keywords=sales+manager&pplSearchOrigin=GLHD&pageKey=member-home

It's so fast and do the same thing.

Upvotes: 47

Views: 89067

Answers (4)

richardtallent
richardtallent

Reputation: 35394

I know this is a very old question, but here's a solution I just used. I have a stored procedure that returns a PAGE of search results, and I also need it to return the total count matching the query parameters.

WITH results AS (...complicated foo here...)

SELECT results.*,
    CASE 
      WHEN @page=0 THEN (SELECT COUNT(*) FROM results)
      ELSE -1
    END AS totalCount 
FROM results
ORDER BY bar
OFFSET @page * @pageSize ROWS FETCH NEXT @pageSize ROWS ONLY;

With this approach, there's a small "hit" on the first results page to get the count, and for the remaining pages, I pass back "-1" to avoid the hit (I assume the number of results won't change during the user session). Even though totalCount is returned for every row of the first page of results, it's only computed once.

My CTE is doing a bunch of filtering based on stored procedure arguments, so I couldn't just move it to a view and query it twice. This approach allows avoid having to duplicate the CTE's logic just to get a count.

Upvotes: 8

Doug S
Doug S

Reputation: 10315

Do a UNION ALL to do multiple SELECT and concatenate the results together into one table.

  ;WITH CTE AS(
         SELECT
         FROM Student
         INNER JOIN ...
         INNER JOIN ....)
  SELECT CityId,City,Count(studentId),NULL,NULL
         FROM CTE
         GROUP BY CityId,City
  UNION ALL
  SELECT NULL,NULL,NULL,GenderId,Gender,Count
         FROM CTE
         GROUP BY GenderId,Gender

Note: The NULL values above just allow the two results to have matching columns, so the results can be concatenated.

Upvotes: 13

Eli Algranti
Eli Algranti

Reputation: 9007

It is not possible to get multiple result sets from a single CTE. You can however use a table variable to cache some of the information and use it later instead of issuing the same complex query multiple times:

declare @relevantStudent table (StudentID int);

insert into @relevantStudent
select s.StudentID from Students s
join ...
where ...

-- now issue the multiple queries

select s.GenderID, count(*)
from student s
join @relevantStudent r on r.StudentID = s.StudentID
group by s.GenderID

select s.CityID, count(*)
from student s
join @relevantStudent r on r.StudentID = s.StudentID
group by s.CityID

The trick is to store only the minimum required information in the table variable.
As with any query whether this will actually improve performance vs. issuing the queries independently depends on many things (how big the table variable data set is, how complex is the query used to populate it and how complex are the subsequent joins/subselects against the table variable, etc.).

Upvotes: 16

Shailesh
Shailesh

Reputation: 1218

You can not use multiple select but you can use more than one CTE like this.

WITH CTEA
AS
(
SELECT 'Coulmn1' A,'Coulmn2' B
),
CETB
AS
(
SELECT 'CoulmnX' X,'CoulmnY' Y
)

SELECT * FROM CTEA, CETB

For getting count use RowNumber and CTE some think like this.

ROW_NUMBER() OVER ( ORDER BY COLUMN NAME )AS RowNumber,
Count(1) OVER() AS TotalRecordsFound

Please let me know if you need more information on this.

Sample for your reference.

With CTE AS (
         Select StudentId, S.CityId, S.GenderId
         FROM Student S
         Inner JOIN CITY C
         ON S.CityId = C.CityId
         INNER JOIN GENDER G
         ON S.GenderId = G.GenderId)
,
GENDER
AS
(
  SELECT GenderId
  FROM CTE
  GROUP BY  GenderId
  )


SELECT * FROM GENDER, CTE

Upvotes: 36

Related Questions