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»»

SQL Query Expand / Collapse
Author
Message
Posted Saturday, July 27, 2013 8:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 28, 2013 11:01 AM
Points: 6, 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
Post #1478328
Posted Saturday, July 27, 2013 9:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 35,366, Visits: 31,906
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1478330
Posted Sunday, July 28, 2013 4:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
Do what Jeff said but replace
PARTITION BY StudentName

With
PARTITION BY SubjectName




Dird // Junior DBA
11g OCA
10.5 newbie
Post #1478342
Posted Sunday, July 28, 2013 5:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 28, 2013 11:01 AM
Points: 6, 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
Post #1478345
Posted Sunday, July 28, 2013 5:57 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:05 PM
Points: 1,034, Visits: 6,806
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
Post #1478346
Posted Sunday, July 28, 2013 6:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
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 // Junior DBA
11g OCA
10.5 newbie
Post #1478347
Posted Sunday, July 28, 2013 6:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:05 PM
Points: 1,034, Visits: 6,806
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
Post #1478349
Posted Sunday, July 28, 2013 6:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 28, 2013 11:01 AM
Points: 6, 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
Post #1478350
Posted Sunday, July 28, 2013 6:21 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 35,366, Visits: 31,906
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1478351
Posted Sunday, July 28, 2013 6:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:05 PM
Points: 1,034, Visits: 6,806
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
Post #1478353
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse