can i get these results with out temp table
-
SELECT college_id,department_id,student_id,SUM(marks) as SUM
FROM student a INNER JOIN college
ON a.college_id=b.college_id
GROUP BY college_id,department_id
Say the above query returns the following values
college_id student_id marks
111111111 567 487
111111111 678 438
111111111 784 398
111111111 651 491
111111111 391 372
and I need to get the max and min value of the marks given above.
So I am doing the following things
SELECT college_id,department_id,student_id,SUM(marks) as SUM
INTO #temp
FROM student a INNER JOIN college
ON a.college_id=b.college_id
GROUP BY college_id,department_id
SELECT * #temp
SELECT MAX(sum),MIN(SUM) FROM #temp
I need both the above results. SO can i do this without using temp table.
-
Select Min(dtMM.marks) as MinM, Max(dtMM.marks) as MaxM from
(
SELECT college_id,department_id,student_id,SUM(marks) as SUM
FROM student a INNER JOIN college
ON a.college_id=b.college_id
GROUP BY college_id,department_id
) dtMM
-
I need to get all the result set and max ,min values,not only max and min.
SELECT * #temp
SELECT MAX(sum),MIN(SUM) FROM #temp.
I need results of both the results without using temp table.
-
How are you gonna use that??
I would keep the original select, then use output parameters for the 2 others... or use the reporting tools to get those values.
-
Do it at Client side !
* Noel -
SELECT college_id,department_id,student_id,SUM(marks) as SUM
INTO #temp
FROM student a INNER JOIN college
ON a.college_id=b.college_id
GROUP BY college_id,department_id
SELECT * #temp
SELECT MAX(sum),MIN(SUM) FROM #temp
I am doing as above instead as shown below.
SELECT college_id,department_id,student_id,SUM(marks) as SUM
FROM student a INNER JOIN college
ON a.college_id=b.college_id
GROUP BY college_id,department_id
SELECT MIN(sum) AS mIN,MAX(SUM) AS MAX FROM(
SELECT college_id,department_id,student_id,SUM(marks) as SUM
FROM student a INNER JOIN college
ON a.college_id=b.college_id
GROUP BY college_id,department_id)a
But I read about the temp tables that they would hurt the performance and should not be used unless they are required. IN this case I felt that temp table is required and used it.I thought that instead of selecting the same data two times (as shown in second case),select the result into a temp table and then work with temp table.Do u think that it will effect the performance of my application.
-
If we got 100 bucks everytime we said that
. -
Yep,
When a man is shown how to use a hammer, suddenly all problems look like nails
* Noel -
Glad we didn't show 'em how to use the sledge hammer.
-
My understanding is that the main problem is not temp tables as such but SELECT INTO #temp because that takes locks on the tempdb system tables while the select is running. You should use CREATE #temp ... and then INSERT INTO #temp ...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply