SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Query


SQL Query

Author
Message
vshiva2379
vshiva2379
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 8
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)

Group: General Forum Members
Points: 208833 Visits: 41973
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Dird
Dird
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1193 Visits: 821
Do what Jeff said but replace
PARTITION BY StudentName



With
PARTITION BY SubjectName




Dird
vshiva2379
vshiva2379
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 8
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
ChrisM@home
ChrisM@home
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5030 Visits: 10605
vshiva2379 (7/28/2013)
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


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.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Dird
Dird
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1193 Visits: 821
select name, 
case
when maths > science and maths > comp then 'maths'
when comp > science and comp > maths then 'comp'
else 'science'
end
from student




Dird
ChrisM@home
ChrisM@home
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5030 Visits: 10605
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.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
vshiva2379
vshiva2379
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 8
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)SSC Guru (208K reputation)

Group: General Forum Members
Points: 208833 Visits: 41973
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ChrisM@home
ChrisM@home
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5030 Visits: 10605
vshiva2379 (7/28/2013)
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


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




Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search