July 27, 2013 at 8:06 pm
Hi all,
suppose if we have student name, and subjects so we want student name and max marks scored subject for each student
one student will be scored in maths, another one scored in science like that
so i want student name, maths
student name, science
Thank you
July 27, 2013 at 9:04 pm
vshiva2379 (7/27/2013)
Hi all,suppose if we have student name, and subjects so we want student name and max marks scored subject for each student
one student will be scored in maths, another one scored in science like that
so i want student name, maths
student name, science
Thank you
Welcome aboard! For future posts, please see the article at the first link in my signature line below.
Since you're new...
--===== WITHOUT Ties
WITH
cteEnumerateScores AS
(
SELECT StudentName, SubjectName, Score,
ScoreOrder = RANK() OVER (PARTITION BY StudentName ORDER BY Score DESC)
FROM dbo.YourTable
)
SELECT StudentName, SubjectName, Score
FROM cteEnummerateScores
WHERE ScoreOrder = 1
ORDER BY StudentName
;
Of course, since I don't know the actual names of your columns or your table, you'll need to make some changes.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 28, 2013 at 4:26 am
Do what Jeff said but replace
PARTITION BY StudentName
With
PARTITION BY SubjectName
July 28, 2013 at 5:43 am
Thank you, But Sorry I didnt Get my result with that
My table is
Student
StudentID int,
name varchar(20),
maths int,
science int,
comp int
records are
1, Allen, 60, 80,75
2, John ,70,60,90
3, Kim, 86,85, 80
result should be
Allen science
John comp
Kim maths
Thank you
July 28, 2013 at 5:57 am
vshiva2379 (7/28/2013)
Thank you, But Sorry I didnt Get my result with thatMy table is
Student
StudentID int,
name varchar(20),
maths int,
science int,
comp int
records are
1, Allen, 60, 80,75
2, John ,70,60,90
3, Kim, 86,85, 80
result should be
Allen science
John comp
Kim maths
Thank you
SQL can't easily get any simpler, hence this must be coursework. What have you tried? If you set up some sample data in the form of a CREATE TABLE statement followed by INSERTs to populate the table, I'm sure someone will help.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
July 28, 2013 at 6:00 am
select name,
case
when maths > science and maths > comp then 'maths'
when comp > science and comp > maths then 'comp'
else 'science'
end
from student
July 28, 2013 at 6:03 am
Dird (7/28/2013)
select name,
case
when maths > science and maths > comp then 'maths'
when comp > science and comp > maths then 'comp'
else 'science'
end
from student
I think you need another edit 😉
Edit: beat me to it.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
July 28, 2013 at 6:19 am
Execuse me
Since you did not understand i how given that data and more over i do not want to disclose my original data i have given that, if you insist then have a look
Create table Student(Studentid int,name varchar(50),maths int,science int,com int)
insert into Student values(1,'Allen',40,50,89)
insert into Student values(2,'John',70,90,89)
insert into Student values(1,'Nick',90,80,89)
Thank you
July 28, 2013 at 6:21 am
Dird (7/28/2013)
Do what Jeff said but replace
PARTITION BY StudentName
With
PARTITION BY SubjectName
I read it as wanting to know the best subject for each student hence the partitioning by StudentName. Partitioning by SubjectName would be to find the best student in each subject. Heh... of course, if we had some sample data and expected results for given sample data, we'd know for sure.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 28, 2013 at 6:24 am
vshiva2379 (7/28/2013)
Execuse meSince you did not understand i how given that data and more over i do not want to disclose my original data i have given that, if you insist then have a look
Create table Student(Studentid int,name varchar(50),maths int,science int,com int)
insert into Student values(1,'Allen',40,50,89)
insert into Student values(2,'John',70,90,89)
insert into Student values(1,'Nick',90,80,89)
Thank you
SELECT Name, x.*
FROM Student
CROSS APPLY (
SELECT TOP 1 * FROM (VALUES (maths,'maths'),(science,'science'),(com,'com')) d (Result,subject) ORDER BY Result DESC
) x
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
July 28, 2013 at 6:25 am
Thank you SSC Enthusiastic, I got it What i want
July 28, 2013 at 6:25 am
ChrisM@home (7/28/2013)
I think you need another edit 😉Edit: beat me to it.
Ha :pinch:
July 28, 2013 at 6:26 am
vshiva2379 (7/28/2013)
Thank you, But Sorry I didnt Get my result with thatMy table is
Student
StudentID int,
name varchar(20),
maths int,
science int,
comp int
records are
1, Allen, 60, 80,75
2, John ,70,60,90
3, Kim, 86,85, 80
result should be
Allen science
John comp
Kim maths
Thank you
I'm thinking that you didn't actually read the article at the first link in my signature line below. And I agree with Chris. This looks like coursework (especially since the data is so badly denormalized). Please show us what you've tried. This should be a learning experience, not a hand out. And take the time to post the data in the readily consumable format identified in the article. That article will also teach you a couple of neat tricks in SQL
--Jeff Moden
Change is inevitable... Change for the better is not.
July 28, 2013 at 6:30 am
Sorry Jeff , That was my mistake i didnt give correct details,
Thanks allot for your valuable time
July 28, 2013 at 6:31 am
vshiva2379 (7/28/2013)
Execuse meSince you did not understand i how given that data and more over i do not want to disclose my original data i have given that, if you insist then have a look
Create table Student(Studentid int,name varchar(50),maths int,science int,com int)
insert into Student values(1,'Allen',40,50,89)
insert into Student values(2,'John',70,90,89)
insert into Student values(1,'Nick',90,80,89)
Thank you
Heh... if I insist. 😀 It's your question, bud. Look how fast people responded when you posted it in a readily consumable format.
The question now is, do you actually understand the code that gave you the desired answer?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply