August 17, 2013 at 3:43 am
Hi Friends,
Pls help me out ...
create table sample (
name varchar(100),
Mark1 int,
mark2 int,
mark3 int
)
insert into sample values ('x',10,12,5)
insert into sample values ('y',15,2,5)
insert into sample values ('z',10,12,45)
there are 3 columns mark1,mark2 & mark3
I need to find out in which a student scored high mark.
which means I need output showing the student name & column name of the highest mark.
desired output is
name columnname
x Mark2
y Mark1
z mark3
August 17, 2013 at 3:56 am
WITH unpivoted AS (
SELECT name,
CASE n.n
WHEN 1 THEN Mark1
WHEN 2 THEN mark2
WHEN 3 THEN mark3
END AS Mark,
CASE n.n
WHEN 1 THEN 'Mark1'
WHEN 2 THEN 'Mark2'
WHEN 3 THEN 'Mark3'
END AS MarkName
FROM sample
CROSS JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3) AS n
), numbered AS (
SELECT name, Mark, MarkName,
row_number() OVER (PARTITION BY name ORDER BY Mark DESC) AS rowno
FROM unpivoted
)
SELECT name, MarkName
FROM numbered
WHERE rowno = 1
This solution may be amazingly complex, but this is the price you pay for the incorrect data model. A table is supposed to model a unique entity and a column is supposed to model a unique attribute of the entity, and the latter rule is violated here. The marks should be rows in a table, not colunms.
Thus the first CTE addresses by unpivoting the columns. The second CTE numbers the rows on the marks so that we can select the highest score.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 17, 2013 at 5:23 am
yes I accept that this is the bad design ..
Is there any other possible way??
August 17, 2013 at 6:22 am
What you mean with "Is there any other possible way??" Isn't my solution satisfactory to you?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 17, 2013 at 10:43 am
Another option:
SELECTname, CASE
WHEN Mark1>Mark2 AND Mark1>Mark3 THEN 'Mark1'
WHEN Mark2>Mark3 THEN 'Mark2'
ELSE 'Mark3' END
FROMSample;
SQL Server Noobie
August 17, 2013 at 11:37 am
jethrow (8/17/2013)
Another option:
SELECTname, CASEWHEN Mark1>Mark2 AND Mark1>Mark3 THEN 'Mark1'
WHEN Mark2>Mark3 THEN 'Mark2'
ELSE 'Mark3' END
FROMSample;
CASE: My favorite 4-letter TSQL word!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 17, 2013 at 7:15 pm
TheSQLGuru (8/17/2013)
jethrow (8/17/2013)
Another option:
SELECTname, CASEWHEN Mark1>Mark2 AND Mark1>Mark3 THEN 'Mark1'
WHEN Mark2>Mark3 THEN 'Mark2'
ELSE 'Mark3' END
FROMSample;
CASE: My favorite 4-letter TSQL word!! 😎
Especially in this "case". 😛
Just so that everyone knows what Kevin and my elation on this is... as "inelegant" as it may seem, it's nasty fast even when more than 3 columns are involved. There's been a number of similar posts where some folks got pretty deep into performance testing of various methods to do this type of thing. Case will pretty much take on all comers for performance.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2013 at 7:16 pm
jethrow (8/17/2013)
Another option:
SELECTname, CASEWHEN Mark1>Mark2 AND Mark1>Mark3 THEN 'Mark1'
WHEN Mark2>Mark3 THEN 'Mark2'
ELSE 'Mark3' END
FROMSample;
Welcome aboard!
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2013 at 2:53 am
Jeff Moden (8/17/2013)
Just so that everyone knows what Kevin and my elation on this is... as "inelegant" as it may seem, it's nasty fast even when more than 3 columns are involved. There's been a number of similar posts where some folks got pretty deep into performance testing of various methods to do this type of thing. Case will pretty much take on all comers for performance.
I like to point out that I suggested the solution I did, not because of performance, but to highlight the bad database design. CASE is likely to be faster than unpivoting in the query. But you need the unpivot when you migrate your data to your new and improved data model.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 19, 2013 at 1:19 am
this gives the ans but difficult to understand that's why....
August 19, 2013 at 3:10 am
If 2 marks are same values, how to query?
August 19, 2013 at 3:16 am
Can you provide more info on input & desired output?
SQL Server Noobie
August 19, 2013 at 3:22 am
vignesh.ms (8/19/2013)
this gives the ans but difficult to understand that's why....
I assume that this is response to my post.
I suggest that you study the query piece by piece. The first part transforms the column to rows. This is a standard way of writing an unpivot query. (There is an UNPIVOT operator, but I never remember the syntax.) You have a table with numbers that spans at least as many rows as there are columns to unpivot, and then you cross-join that table with the base table to get as many rows you need. And then you map a number to one of the columns.
The second part, numbers the rows and then I select the row with number 1. This is a standard mechanism when you want values for max date, id or whatever.
Thus, my query gives you two very useful query techniques that you can reuse many times.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 19, 2013 at 4:15 am
Yes Erland Sommarskog..
Thanks for your kind response & the assistance....
Well said ... I'm newbie to sql ...
your query uncovers some new query techniques to me
thanks again..
August 19, 2013 at 4:24 am
@vignesh.ms ,
I have to ask... was there something that the simple CASE statement solution wasn't doing for you?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply