Sql Server Max function on multiple columns?

Question

Below is my table data

 Mat   Phy Chem
 20 30  40
 25 35  35
 45 30  30
 45 40  35

I want to retrieve top 3 max rows of all the three columns in a single row.

O/P

 Mat  Phy Chem
 45   40   40
 25   35   35
 20   30   30

I have used below query but was unsuccessful, please help...

Select distinct top 3 max(mat) from studata group by mat order by max(mat) desc
Union all
Select distinct top 3 max(phy) from studata group by phy order by max(phy) desc
Union all
Select distinct top 3 max(chem) from studata group by chem order by max(chem) desc

Quassnoi · Accepted Answer · 2011-03-31 14:45:44Z

2

WITH    q AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (ORDER BY mat DESC) AS rn_mat,
                ROW_NUMBER() OVER (ORDER BY phy DESC) AS rn_phy,
                ROW_NUMBER() OVER (ORDER BY chem DESC) AS rn_chem
        FROM    mytable
        )
SELECT  q_mat.mat, q_phy.phy, q_chem.chem
FROM    q q_mat
JOIN    q q_phy
ON      q_phy.rn_phy = q_mat.rn_mat
JOIN    q q_chem
ON      q_chem.rn_chem = q_phy.rn_phy
WHERE   q_mat.rn_mat <= 3

edited Mar 31, 2011 at 14:45

answered Mar 31, 2011 at 14:34

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lamak Over a year ago

I think that you should use DENSE_RANK instead of ROW_NUMBER, since the op can have the same value for each column and it wouldn't retrieve the top 3 distinct max.

Simhadri Over a year ago

I have done some little changes WITH q AS ( SELECT *, DENSE_RANK() OVER (ORDER BY mat ) AS rn_mat, DENSE_RANK() OVER (ORDER BY phy ) AS rn_phy, DENSE_RANK() OVER (ORDER BY chem ) AS rn_chem FROM studata ) SELECT distinct q_mat.mat, q_phy.phy, q_chem.chem FROM q q_mat JOIN q q_phy ON q_phy.rn_phy = q_mat.rn_mat JOIN q q_chem ON q_chem.rn_chem = q_phy.rn_phy WHERE q_mat.rn_mat <= 3 order by q_mat.mat desc, q_phy.phy desc, q_chem.chem desc

Simhadri Over a year ago

@Quassnoi Can you explain how it works, this is the first time I have used this type of query.As im a beginner in SQL Server. Thanks in advance..

m.edmondson · Accepted Answer · 2011-03-31 14:43:56Z

0

Does this work?

select distinct top 3 Mat
from studata
order by Mat desc

select distinct top 3 Phy
from studata
order by Phy desc

select distinct top 3 Chem
from studata
order by Chem desc

The reason you're probably getting a problem is because max is a function (it will only ever return 1 thing) so Select distinct top 3 max(mat) is nonsense.

edited Mar 31, 2011 at 14:43

answered Mar 31, 2011 at 14:32

m.edmondson

30.9k29 gold badges129 silver badges212 bronze badges

4 Comments

Simhadri Over a year ago

thanks for the reply,It results only 1 row, i need to disply top 3 rows.

m.edmondson Over a year ago

See my updated answer, because you want to separate the rows up you'll need three separate SELECTs. If you really need it together, investigate temporary tables

Lamak Over a year ago

you do need a DISTINCT somewhere, since this way you can have the same value on the top 3, and that's not what the op wants

m.edmondson Over a year ago

@Lamak - You're correct I overlooked that, I've modified my answer

Collectives™ on Stack Overflow

Sql Server Max function on multiple columns?

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related