July 18, 2011 at 6:45 am
Hi All,
I have a requirement like, we are having two tables in our database.
Table names: student, marklist
Student table values:
id studname
------------------
1x
2y
3z
4a
5b
Marklist table values:
id maths physics English
---------------------------------
1506070
2706040
3508070
45010070
5906070
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
July 18, 2011 at 8:00 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 18, 2011 at 8:46 am
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
July 18, 2011 at 8:47 am
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
July 19, 2011 at 4:41 am
Hi srikant maurya
Thanks for your valuable suggestion to acheive the scenario.
Thank you very much
Best Regards
Radh
July 20, 2011 at 8:10 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 20, 2011 at 8:19 am
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!
June 21, 2013 at 6:11 am
SELECT StudentName,Class,MAX(Marks) AS MAX_Marks FROM Student123
GROUP BY StudentName,Class
ORDER BY Class
June 21, 2013 at 5:00 pm
chkri.panati (6/21/2013)
SELECT StudentName,Class,MAX(Marks) AS MAX_Marks FROM Student123GROUP 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
Change is inevitable... Change for the better is not.
January 31, 2015 at 6:50 pm
select tbl.id, st.studname,tbl.subject
from Student st
join
(
select id, 'maths' as subject from Marklist where maths= (select max(maths) from Marklist )
union
select id, 'physics' as subject from Marklist where physics = (select max(physics) from Marklist )
union
select id, 'English' as subject from Marklist where English = (select max(English) from Marklist )
)
tbl
on st.id = tbl.id
February 1, 2015 at 5:27 pm
An effective method is a CROSS APPLY VALUES UNPIVOT (described in my signature links):
WITH Students (id, studname) AS
(
SELECT 1, 'x'
UNION ALL SELECT 2, 'y'
UNION ALL SELECT 3, 'z'
UNION ALL SELECT 4, 'a'
UNION ALL SELECT 5, 'b'
),
Markslist (id, maths, physics, English) AS
(
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
)
SELECT studname, [subject], score
FROM
(
SELECT studname, [subject], score
,rn=ROW_NUMBER() OVER (PARTITION BY [subject] ORDER BY score DESC)
FROM Students a
JOIN MarksList b ON a.id = b.id
CROSS APPLY
(
VALUES (maths, 'maths'), (physics, 'physics'), (English, 'English')
) c (score, [subject])
) a
WHERE rn=1;
Using ROW_NUMBER() instead of GROUP BY will even tell you the "stud" whose name got the high score.
If there's a tie for high score and you want to show that, use RANK() instead of ROW_NUMBER().
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply