GROUP BY question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

GROUP BY question

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-03-09 : 03:55:19

Hi Team,

I have a MarkTable as follows. I want to see the students’ names who have the least mark in each subject. The expected result is (Biology,17, Thomason) and (Maths,6, Nilson)
Is there a way to achieve this without using sub queries?

DECLARE @MarkTable TABLE (MarkEntryId INT, SubjectName VARCHAR(10), StudentName VARCHAR(10), Mark INT)

INSERT INTO @MarkTable VALUES (1,'Biology','Hari',35)
INSERT INTO @MarkTable VALUES (2,'Biology','Thomason',17)
INSERT INTO @MarkTable VALUES (3,'Maths','Hari',49)
INSERT INTO @MarkTable VALUES (5,'Maths','Steve',9)
INSERT INTO @MarkTable VALUES (4,'Maths','Nilson',6)

SELECT SubjectName, MIN(Mark)
FROM @MarkTable
GROUP BY SubjectName

Please help..

[I understand that, the above table design is not normalized. This only for learning purpose]

Thanks
Lijo Cheeran Joseph

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-09 : 04:06:57

SELECT t1.* FROM @MarkTable as t1
inner join
(
SELECT SubjectName, MIN(Mark) as Mark
FROM @MarkTable
GROUP BY SubjectName
) as t2 on t1.SubjectName=t2.SubjectName and t1.Mark=t2.Mark

Madhivanan

Failing to plan is Planning to fail

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-09 : 04:07:22

select s.SubjectName,s.mark,m.StudentName from @MarkTable m
inner join
(SELECT SubjectName, MIN(Mark) as mark
FROM @MarkTable
GROUP BY SubjectName)s on s.mark=m.Mark

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-09 : 04:09:54

quote:
Originally posted by senthil_nagore

select s.SubjectName,s.mark,m.StudentName from @MarkTable m
inner join
(SELECT SubjectName, MIN(Mark) as mark
FROM @MarkTable
GROUP BY SubjectName)s on s.mark=m.Mark

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

You need to join SubjectName too

Madhivanan

Failing to plan is Planning to fail

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-09 : 04:19:31

quote:
Originally posted by madhivanan

quote:
Originally posted by senthil_nagore

select s.SubjectName,s.mark,m.StudentName from @MarkTable m
inner join
(SELECT SubjectName, MIN(Mark) as mark
FROM @MarkTable
GROUP BY SubjectName)s on s.mark=m.Mark

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

You need to join SubjectName too

Madhivanan

Failing to plan is Planning to fail

Yes, i miss it out

select s.SubjectName,s.mark,m.StudentName from @MarkTable m
inner join
(SELECT SubjectName, MIN(Mark) as mark
FROM @MarkTable
GROUP BY SubjectName)s on s.mark=m.Mark and s.SubjectName=m.SubjectName

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-03-09 : 05:52:31

Thanks for the answer. It works.

I hope, you will be kind to answer the following extension of the question as well.

Suppose I have one more table @PreviousMarkTable. When a student ask for re-evaluation of the answer sheet, the previous mark is moved into the @PreviousMarkTable. That means, if there is a record for a student for a subject in @PreviousMarkTable, there will be a latest record in @MarkTable.

For each subject, I would like to know the mimimum mark(minimum of both table) and the corresponding studentname. How can this be achieved without using sub queries.

DECLARE @MarkTable TABLE (MarkEntryId INT, SubjectName VARCHAR(10), StudentName VARCHAR(10), Mark INT)
INSERT INTO @MarkTable VALUES (1,'Biology','Hari',35)
INSERT INTO @MarkTable VALUES (2,'Biology','Thomason',6)
INSERT INTO @MarkTable VALUES (5,'Maths','Steve',9)
INSERT INTO @MarkTable VALUES (4,'Maths','Nilson',6)

DECLARE @PreviousMarkTable TABLE (PrevMarkEntryId INT, SubjectName VARCHAR(10), StudentName VARCHAR(10), Mark INT)
INSERT INTO @PreviousMarkTable VALUES (1,'Biology','Hari',5)
INSERT INTO @PreviousMarkTable VALUES (1,'Maths','Nilson',7)

Expected Result
(Biology,5,Hari) and (Maths,6,Nilson)

Please help…

Thanks
Lijo Cheeran Joseph

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-09 : 06:10:53

Try this!

select t1.SubjectName,t1.mark,t2.StudentName from
(select SubjectName, min(mark) as mark from
(SELECT SubjectName, mark,StudentName
FROM @MarkTable union all
SELECT SubjectName, mark,StudentName
FROM @PreviousMarkTable) s group by SubjectName) t1
inner join (SELECT SubjectName, mark,StudentName
FROM @MarkTable union all
SELECT SubjectName, mark,StudentName
FROM @PreviousMarkTable) t2 on t1.mark=t2.Mark and t1.SubjectName=t2.SubjectName

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-09 : 06:20:43

quote:
Originally posted by Lijo Cheeran Joseph

Thanks for the answer. It works.

I hope, you will be kind to answer the following extension of the question as well.

Suppose I have one more table @PreviousMarkTable. When a student ask for re-evaluation of the answer sheet, the previous mark is moved into the @PreviousMarkTable. That means, if there is a record for a student for a subject in @PreviousMarkTable, there will be a latest record in @MarkTable.

For each subject, I would like to know the mimimum mark(minimum of both table) and the corresponding studentname. How can this be achieved without using sub queries.

DECLARE @MarkTable TABLE (MarkEntryId INT, SubjectName VARCHAR(10), StudentName VARCHAR(10), Mark INT)
INSERT INTO @MarkTable VALUES (1,'Biology','Hari',35)
INSERT INTO @MarkTable VALUES (2,'Biology','Thomason',6)
INSERT INTO @MarkTable VALUES (5,'Maths','Steve',9)
INSERT INTO @MarkTable VALUES (4,'Maths','Nilson',6)

DECLARE @PreviousMarkTable TABLE (PrevMarkEntryId INT, SubjectName VARCHAR(10), StudentName VARCHAR(10), Mark INT)
INSERT INTO @PreviousMarkTable VALUES (1,'Biology','Hari',5)
INSERT INTO @PreviousMarkTable VALUES (1,'Maths','Nilson',7)

Expected Result
(Biology,5,Hari) and (Maths,6,Nilson)

Please help…

Thanks
Lijo Cheeran Joseph

Could you please check thsi is wht you want?

DECLARE @MarkTable TABLE (MarkEntryId INT, SubjectName VARCHAR(10), StudentName VARCHAR(10), Mark INT)
INSERT INTO @MarkTable VALUES (1,'Biology','Hari',35)
INSERT INTO @MarkTable VALUES (2,'Biology','Thomason',6)
INSERT INTO @MarkTable VALUES (5,'Maths','Steve',9)
INSERT INTO @MarkTable VALUES (4,'Maths','Nilson',6)

DECLARE @PreviousMarkTable TABLE (PrevMarkEntryId INT, SubjectName VARCHAR(10), StudentName VARCHAR(10), Mark INT)
INSERT INTO @PreviousMarkTable VALUES (1,'Biology','Hari',5)
INSERT INTO @PreviousMarkTable VALUES (1,'Maths','Nilson',7)

select * from @MarkTable
select * from @PreviousMarkTable

select t1.MarkEntryId,t1.SubjectName,t1.StudentName ,
case when t1.Mark > t2.Mark then t2.Mark else t1.Mark end as Marks from @MarkTable as t1 inner join
(SELECT SubjectName, MIN(Mark) as Mark,StudentName
FROM @PreviousMarkTable
GROUP BY SubjectName,StudentName)as t2 on t1.SubjectName=t2.SubjectName and t1.StudentName=t2.StudentName

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-09 : 06:29:07

Hi,
I'd would like to know my approach is correct(Ref:See my previous reply here)for this kind of Approach?... Though iam a beginner of sql..
Yoour inputs are awlways welcome.

Looking Fwd to hear from you...

Thanks,
Haroon

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-03-09 : 07:57:27

Thanks for the hints. The following works

--Create temporary table
CREATE TABLE #EntireMarks (SubjectName VARCHAR(10), Mark INT, StudentName VARCHAR(10))

INSERT INTO #EntireMarks
SELECT SubjectName, Mark,StudentName FROM @MarkTable
UNION ALL
SELECT SubjectName, Mark,StudentName FROM @PreviousMarkTable

SELECT t1.SubjectName,t1.mark,t2.StudentName
FROM
(
SELECT SubjectName, MIN(mark) as mark
FROM #EntireMarks s
GROUP BY SubjectName
) t1
INNER JOIN #EntireMarks t2
ON t1.mark=t2.Mark
AND t1.SubjectName=t2.SubjectName

Haroon's approach gives correct result. However I have a special requirement that I should not use StudentName in the GROUP BY.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-09 : 08:08:35

quote:
Originally posted by Lijo Cheeran Joseph

Thanks for the hints. The following works

--Create temporary table
CREATE TABLE #EntireMarks (SubjectName VARCHAR(10), Mark INT, StudentName VARCHAR(10))

INSERT INTO #EntireMarks
SELECT SubjectName, Mark,StudentName FROM @MarkTable
UNION ALL
SELECT SubjectName, Mark,StudentName FROM @PreviousMarkTable

SELECT t1.SubjectName,t1.mark,t2.StudentName
FROM
(
SELECT SubjectName, MIN(mark) as mark
FROM #EntireMarks s
GROUP BY SubjectName
) t1
INNER JOIN #EntireMarks t2
ON t1.mark=t2.Mark
AND t1.SubjectName=t2.SubjectName

Haroon's approach gives correct result. However I have a special requirement that I should not use StudentName in the GROUP BY.

Hey,you can avoid of creating temp tables and drop it at the end..please see below of using cte
; with cte(SubjectName,Mark,StudentName)
as
(
SELECT SubjectName, Mark,StudentName FROM @MarkTable
UNION
SELECT SubjectName, Mark,StudentName FROM @PreviousMarkTable
)
SELECT t1.SubjectName,t1.mark,t2.StudentName
FROM
(
SELECT SubjectName, MIN(mark) as mark
FROM cte as s
GROUP BY SubjectName
) t1
INNER JOIN cte t2
ON t1.mark=t2.Mark
AND t1.SubjectName=t2.SubjectName

HTH.

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-03-09 : 08:22:39

quote:

Hey,you can avoid of creating temp tables and drop it at the end..

I agree that the temp table can be avoided by using CTE. However, you might have noticed the use of #EntireMarks, twice. I think, in this scenario it is better to use temporary table than using CTE. (CTE does not materialize).

This is my thought only – Anticipate comments from experts.

Thanks
Lijo

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-09 : 08:40:28

What Others view on this?Eager to know your view please.
Your comments really much appreaciated here..
here temp table or CTE or Table Variable or wat?

what is the best way(Interms of Performance oriented?)for this kinda scenario?........

Eager to know comments Please..

Your view actually much help me to write the queries in a good way in future...

thanks,
Haroon

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-03-09 : 08:51:06

I used the Ctrl+L with SQL Server Management Studio to see the estimated cost of execution plan. The idea 1 (using temp table) took 11%+10% = 21 %. The Idea 2 (using CTE) took 37 %
It seems like the temporary table approach performs better.

Also, is there a way to see plans for these two approaches as two batches. Currently my execution plan has different sections. The temporary table approach is the combination of INSERT(11%) and SELECT(10%)

The combined query is listed below

DECLARE @MarkTable TABLE (MarkEntryId INT, SubjectName VARCHAR(10), StudentName VARCHAR(10), Mark INT)
INSERT INTO @MarkTable VALUES (1,'Biology','Hari',35)
INSERT INTO @MarkTable VALUES (2,'Biology','Thomason',6)
INSERT INTO @MarkTable VALUES (5,'Maths','Steve',9)
INSERT INTO @MarkTable VALUES (4,'Maths','Nilson',6)

DECLARE @PreviousMarkTable TABLE (PrevMarkEntryId INT, SubjectName VARCHAR(10), StudentName VARCHAR(10), Mark INT)
INSERT INTO @PreviousMarkTable VALUES (1,'Biology','Hari',5)
INSERT INTO @PreviousMarkTable VALUES (1,'Maths','Nilson',7)

--Idea 1
CREATE TABLE #EntireMarks (SubjectName VARCHAR(10), Mark INT, StudentName VARCHAR(10))

INSERT INTO #EntireMarks
SELECT SubjectName, Mark,StudentName FROM @MarkTable
UNION ALL
SELECT SubjectName, Mark,StudentName FROM @PreviousMarkTable

SELECT t1.SubjectName,t1.mark,t2.StudentName
FROM
(
SELECT SubjectName, MIN(mark) as mark
FROM #EntireMarks s
GROUP BY SubjectName
) t1
INNER JOIN #EntireMarks t2
ON t1.mark=t2.Mark
AND t1.SubjectName=t2.SubjectName

--- Idea 2
; with cte(SubjectName,Mark,StudentName)
as
(
SELECT SubjectName, Mark,StudentName FROM @MarkTable
UNION
SELECT SubjectName, Mark,StudentName FROM @PreviousMarkTable
)
SELECT t1.SubjectName,t1.mark,t2.StudentName
FROM
(
SELECT SubjectName, MIN(mark) as mark
FROM cte as s
GROUP BY SubjectName
) t1
INNER JOIN cte t2
ON t1.mark=t2.Mark
AND t1.SubjectName=t2.SubjectName

DROP TABLE #EntireMarks

Thanks
Lijo Cheeran Joseph

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources