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

Retrives the subject wise highest marks list in a table Expand / Collapse
Author
Message
Posted Monday, July 18, 2011 6:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 3:13 AM
Points: 13, Visits: 109
Hi All,

I have a requirement like, we are having two tables in our database.
Table names: student, marklist

Student table values:

id studname
------------------
1 x
2 y
3 z
4 a
5 b

Marklist table values:

id maths physics English
---------------------------------
1 50 60 70
2 70 60 40
3 50 80 70
4 50 100 70
5 90 60 70

But my requirement is, I need to display the data "subject wise" highest marks for each student.

for example:

id name highestmark
---------------------------------
1 x English


Any boxy help me how to reach this scenario.


Thanks in Advance

Best regards
Radh
Post #1143380
Posted Monday, July 18, 2011 8:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 11,970, Visits: 10,995
This looks a lot like homework. What have you tried so far?

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1143427
Posted Monday, July 18, 2011 8:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:29 AM
Points: 1,000, Visits: 1,041
Check Below SQL and let me know if it's work for you

use tempdb;
go
create table stu( id int , name varchar(10))
insert into stu(id,name)
select 1 id, 'x' name union all
select 2, 'y' union all
select 3 , 'z' union all
select 4, 'a' union all
select 5, 'b'

create table mark(id int ,maths int, physics int ,English int)

insert into mark
select 1, 50, 60, 70 union all
select 2 ,70 ,60, 40 union all
select 3, 50, 80, 70 union all
select 4 ,50 ,100, 70 union all
select 5 ,90, 60, 70


go
SELECT id,
subject,
MARK,
Row_number() OVER ( ORDER BY id, MARK DESC ) AS row_id
into #t
FROM
(SELECT id,
Convert(VARCHAR(15), subject) [subject],
MARK
FROM
(SELECT id,
maths,
physics,
english
FROM
MARK) p UNPIVOT (MARK FOR [subject] IN (maths, physics, english) )AS unpvt) t

GO

SELECT t.*
FROM
#t t
INNER JOIN (SELECT id,
Min(row_id) row_id
FROM
#t
GROUP BY id)tt
ON t.row_id = tt.row_id



Post #1143467
Posted Monday, July 18, 2011 8:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:29 AM
Points: 1,000, Visits: 1,041
Check Below SQL and let me know if it's work for you

use tempdb;
go
create table stu( id int , name varchar(10))
insert into stu(id,name)
select 1 id, 'x' name union all
select 2, 'y' union all
select 3 , 'z' union all
select 4, 'a' union all
select 5, 'b'

create table mark(id int ,maths int, physics int ,English int)

insert into mark
select 1, 50, 60, 70 union all
select 2 ,70 ,60, 40 union all
select 3, 50, 80, 70 union all
select 4 ,50 ,100, 70 union all
select 5 ,90, 60, 70


go
SELECT id,
subject,
MARK,
Row_number() OVER ( ORDER BY id, MARK DESC ) AS row_id
into #t
FROM
(SELECT id,
Convert(VARCHAR(15), subject) [subject],
MARK
FROM
(SELECT id,
maths,
physics,
english
FROM
MARK) p UNPIVOT (MARK FOR [subject] IN (maths, physics, english) )AS unpvt) t

GO

SELECT t.*
FROM
#t t
INNER JOIN (SELECT id,
Min(row_id) row_id
FROM
#t
GROUP BY id)tt
ON t.row_id = tt.row_id



Post #1143469
Posted Tuesday, July 19, 2011 4:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 3:13 AM
Points: 13, Visits: 109
Hi srikant maurya

Thanks for your valuable suggestion to acheive the scenario.



Thank you very much

Best Regards
Radh
Post #1143974
Posted Wednesday, July 20, 2011 5:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:09 AM
Points: 1,622, Visits: 2,032
Tell your teacher to stop showing you problems with poorly designed databases.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Post #1144876
Posted Wednesday, July 20, 2011 8:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 11,970, Visits: 10,995
Thomas Abraham (7/20/2011)
Tell your teacher to stop showing you problems with poorly designed databases.


I think that this is major cause of poorly designed tables in the real world. The teachers harp on normalization and then they give garbage like this as an example. Nothing like teaching bad practices as the student is learning. Makes it harder to understand later why that is so bad.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1145041
Posted Wednesday, July 20, 2011 8:19 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
Sean Lange (7/20/2011)
Thomas Abraham (7/20/2011)
Tell your teacher to stop showing you problems with poorly designed databases.


I think that this is major cause of poorly designed tables in the real world. The teachers harp on normalization and then they give garbage like this as an example. Nothing like teaching bad practices as the student is learning. Makes it harder to understand later why that is so bad.


I agree, but then again it gives me more jobs to do after they've put the code to work in prod!
Post #1145057
Posted Friday, June 21, 2013 6:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 28, 2013 3:17 AM
Points: 1, Visits: 15
SELECT StudentName,Class,MAX(Marks) AS MAX_Marks FROM Student123
GROUP BY StudentName,Class
ORDER BY Class
Post #1466149
Posted Friday, June 21, 2013 5:00 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 35,954, Visits: 30,243
chkri.panati (6/21/2013)
SELECT StudentName,Class,MAX(Marks) AS MAX_Marks FROM Student123
GROUP BY StudentName,Class
ORDER BY Class



I'm pretty sure that's not going to work with the original data and table structure given in the original post.


--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." -- 04 August 2013
(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 #1466426
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse