December 16, 2013 at 6:44 am
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
December 16, 2013 at 6:53 am
Put what you've already got in a Common Table Expression, then join to the Subject table to get SubName.
John
December 16, 2013 at 7:12 am
Jonh!
Thank for your attention in this, but would you like to show me how more clearly?
I am a newbie in this language.
December 16, 2013 at 7:50 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 16, 2013 at 8:00 am
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
December 16, 2013 at 8:27 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 16, 2013 at 8:32 am
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
December 16, 2013 at 8:37 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 16, 2013 at 8:44 am
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
December 16, 2013 at 9:27 am
Dear Sean!
Your code is so simple but it very excellent.
It solved all my problem
Thank you so much
December 16, 2013 at 9:30 am
hoanggiathanhsingles (12/16/2013)
Dear Sean!Your code is so simple but it very excellent.
It solved all my problem
Thank you so much
You are quite welcome. Glad it worked for you.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply