SQL Query

Question

SQL Query

vshiva2379

SSC Enthusiast

Points: 168
More actions
July 27, 2013 at 8:06 pm

#395213

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

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dird SSCertifiable Points: 5081 More actions · Answer 2

Do what Jeff said but replace

PARTITION BY StudentName

With

PARTITION BY SubjectName

Dird

vshiva2379 SSC Enthusiast Points: 168 More actions · Answer 3

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 SSC-Insane Points: 24260 More actions · Answer 4

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.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

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

Dird SSCertifiable Points: 5081 More actions · Answer 5

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 SSC-Insane Points: 24260 More actions · Answer 6

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.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

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

vshiva2379 SSC Enthusiast Points: 168 More actions · Answer 7

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 SSC Guru Points: 1003853 More actions · Answer 8

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 9

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

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

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

vshiva2379 SSC Enthusiast Points: 168 More actions · Answer 10

vshiva2379

SSC Enthusiast

Points: 168

July 28, 2013 at 6:25 am

#1636454

Thank you SSC Enthusiastic, I got it What i want

Dird SSCertifiable Points: 5081 More actions · Answer 11

ChrisM@home (7/28/2013)
I think you need another edit 😉
Edit: beat me to it.

Ha :pinch:

Dird

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 12

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

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

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vshiva2379 SSC Enthusiast Points: 168 More actions · Answer 13

Sorry Jeff , That was my mistake i didnt give correct details,

Thanks allot for your valuable time

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 14

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

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

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)