CODEWITHSUNDEEP
sqlsql-server-2005
Simhadri
Simhadri

Reputation: 931

Sql Server Max function on multiple columns?

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

Upvotes: 1

Views: 1541

Answers (2)

Quassnoi
Quassnoi

Reputation: 425371

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

Upvotes: 2

m.edmondson
m.edmondson

Reputation: 30882

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.

Upvotes: 0

Related Questions