Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Retrieve subject name with max mark Expand / Collapse
Author
Message
Posted Monday, December 16, 2013 6:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 16, 2013 9:25 AM
Points: 4, Visits: 5
Dear all
I had a problem when trying to retrieve the name of subject with max mark
I had two table as following:
Subject table:
create table [Subject]
(
SubID int identity(1,1) primary key,
SubName nvarchar(30) not null,
SubCredit tinyint not null constraint Default_value_Subect default(1) constraint Check_value_Subject check(SubCredit>=1),
SubStatus bit constraint default_value_SubStatus default(1)
)
Mark table:
create table Marks
(
MarkID int identity(1,1) primary key,
SubID int foreign key references [Subject](SubID),
StudentID int foreign key references Students(StudentID),
Mark float constraint Check_value_Mark check(Mark>=0 and Mark <=100) constraint Default_value_Mark Default(0),
ExamTime tinyint constraint Default_value_Examtime default(1)
)

I use this SQL statement and i can retrieve SubID with max mark
select Marks.SubID, MAX(Marks.Mark)as diem from Marks group by Marks.SubID

But otherwise, I want to display the name of Subject in stead of SubID
How can i do this? Please show me how!
Thank alot

Post #1523192
Posted Monday, December 16, 2013 6:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:06 AM
Points: 5,430, Visits: 10,108
Put what you've already got in a Common Table Expression, then join to the Subject table to get SubName.

John
Post #1523196
Posted Monday, December 16, 2013 7:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 16, 2013 9:25 AM
Points: 4, Visits: 5
Jonh!
Thank for your attention in this, but would you like to show me how more clearly?
I am a newbie in this language.
Post #1523208
Posted Monday, December 16, 2013 7:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:44 AM
Points: 13,093, Visits: 12,574
No need for a cte to do this. Just join from your Marks table to the Subjects table.



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1523235
Posted Monday, December 16, 2013 8:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:06 AM
Points: 5,430, Visits: 10,108
No need for a cte to do this.

The reason for the CTE is because he wants SubName instead of SubID. Also, if SubName isn't unique, that would mess up the grouping with a straight join.

Thank for your attention in this, but would you like to show me how more clearly?

A Common Table Expression can be thought of as a derived table. So you would start your query like this:

WITH MyCTE AS (
select Marks.SubID, MAX(Marks.Mark)as diem from Marks group by Marks.SubID
)

and then write a query as if MyCTE were the name of a table.

John
Post #1523244
Posted Monday, December 16, 2013 8:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:44 AM
Points: 13,093, Visits: 12,574
John Mitchell-245523 (12/16/2013)
No need for a cte to do this.

The reason for the CTE is because he wants SubName instead of SubID. Also, if SubName isn't unique, that would mess up the grouping with a straight join.



You can easily handle this by adding SubID to the grouping.

insert Subject
select 'Subject One', 3, 1 union all
select 'Subject One', 3, 1

insert Marks
select 1, 1, 88.9, 22 union all
select 2, 2, 55, 1

select s.SubName, MAX(m.Mark)as diem
from Marks m
join Subject s on s.SubID = m.SubID group by s.SubName, s.SubID



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1523263
Posted Monday, December 16, 2013 8:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:06 AM
Points: 5,430, Visits: 10,108
Ah yes, that works. I was thinking you need the same columns in the SELECT list as in the GROUP BY clause, but of course you don't. Thanks Sean.

John
Post #1523267
Posted Monday, December 16, 2013 8:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:44 AM
Points: 13,093, Visits: 12,574
John Mitchell-245523 (12/16/2013)
Ah yes, that works. I was thinking you need the same columns in the SELECT list as in the GROUP BY clause, but of course you don't. Thanks Sean.

John


Yes you can group by additional columns, you just can't show them in the output unless they are grouped.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1523272
Posted Monday, December 16, 2013 8:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 16, 2013 9:25 AM
Points: 4, Visits: 5
Dear all
Thank you all for spending your time in this topic, and now, i've finished this problem by following code:

select distinct [Subject].SubName, Marks.Mark from Marks

inner join [Subject] on Marks.SubID=[Subject].SubID

where Marks.Mark in(select MAX(Marks.Mark)as diem from Marks group by Marks.SubID )


Thank all again for your help! I am so lucky wheng joining this 4rum
Post #1523277
Posted Monday, December 16, 2013 9:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 16, 2013 9:25 AM
Points: 4, Visits: 5
Dear Sean!
Your code is so simple but it very excellent.
It solved all my problem
Thank you so much
Post #1523304
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse