CODEWITHSUNDEEP
sql-serversql-server-2008
codrgi
codrgi

Reputation: 29

order selected columns by ascending order

I have a table like this;

Reg_No      Student_Name         col1    col2    col3
----------- -------------------- ------ ------ ------
101         Kevin                77          94          78
102         Andrew               91          81          17
103         Scott                46          83          28

How am i able to select col1, col2, col3 and arrange those selected values from col1, col2, col3 into ascending order from each row, but keep the order in which the table was accessed so it would look like the following?

Reg_No      Student_Name         Lowest    Middle    Highest
----------- ------------------- --------- --------- ---------
101         Kevin                77          78          94
102         Andrew               17          81          91
103         Scott                28          46          83

I am using MSSQL Server 2008 R2

Upvotes: 2

Views: 73

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93694

Here is one way without generating Row_Number

WITH cte 
     AS (SELECT reg_no, 
                student_name, 
                Max(v.col)OVER(partition BY reg_no, student_name) AS highest, 
                Min(v.col)OVER(partition BY reg_no, student_name) AS lowest, 
                col 
         FROM   Yourtable t 
                CROSS apply (VALUES (t.col1),(t.col2),(t.col3) ) v(col)) 
SELECT reg_no, 
       student_name, 
       lowest=Min(lowest), 
       Middle=Min(CASE WHEN col <> lowest AND col <> highest THEN col END), 
       highest=Max(highest) 
FROM   cte 
GROUP  BY reg_no, 
          student_name

Upvotes: 0

JBrooks
JBrooks

Reputation: 10013

You do need to normalize your data, but till then:

with temp (ColNo, Reg_No, Student_Name, col) as
   select 1, Reg_No, Student_Name, col1
   from Students
   union all
   select 2, Reg_No, Student_Name, col2
   from Students
   union all
   select 3, Reg_No, Student_Name, col3
   from Students;


select 
   min(t1.col) as Lowest,
   max(t2.col) as Middle
   max(t1.col) as Highest
from temp t1
join t2
on t1.Reg_No = t2.Reg_No
   and t1.Student_Name = t1.Student_Name
   and t1.ColNo <> t2.ColNo  -- don't match on self
   and t1.col >= t2.col      -- don't include the max in t2 unless more than 1
group by t1.Reg_No, t1.Student_Name
order by t1.Reg_No, t1.Student_Name

So if the set of (col1, col2, col3) is (1, 2, 3) then t2 ends up being (1, 2), the max of which is 2.

If the set is (3, 3, 1) then t2 ends up being (3, 3, 1), max of which is 3.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

This is sort of painful in SQL Server. One method is to unpivot the data and then re-pivot. I would be inclined to do this using conditional aggregation:

select Reg_No, Student_Name,
       max(case when seqnum = 1 then col end) as Lowest,
       max(case when seqnum = 2 then col end) as Middle,
       max(case when seqnum = 3 then col end) as Highest
from (select t.*, v.col,
             row_number() over (partition by Reg_No, Student_Name order by v.col) as seqnum
      from likethis t cross apply
           (values (t.col1), (t.col2), (t.col3)
           ) v(col)
     ) t
group by Reg_No, Student_Name;

If you attempt to do this with case statements, they will get quite complicated, because of possible ties and NULL values.

Upvotes: 3

Related Questions