CODEWITHSUNDEEP
sqlsql-servert-sql
Kalyn
Kalyn

Reputation: 55

MSSQL get top 4 values from 12 columns

I need some assistance here. I have a result set as per below:

Name | IdNumber | Subject1 |Percentage | Subject2 | Percentage | Subject3 | Percentage | (...until subject 12)

I need to return only the 4 subjects and their percentage that the student scored the highest in.

Getting the highest mark per student is easy, I've done it this way: 

SELECT [Other Fields],
  (SELECT Max(v) 
   FROM (VALUES (date1), (date2), (date3),...) AS value(v)) as [MaxDate]
FROM [YourTableName]

Getting the other 4 is the problem.

Upvotes: 4

Views: 98

Answers (3)

StepUp
StepUp

Reputation: 38199

Try to unpivot data as the data you have is not normalised and then pivot data again.

Preparing data:

DECLARE @FooTable TABLE 
( Name VARCHAR(10), IdNumber INT,
   Subject1 VARCHAR(100), Percentage1 INT,
   Subject2 VARCHAR(10),  Percentage2 INT,
   Subject3 VARCHAR(10),  Percentage3 INT,
   Subject4 VARCHAR(10),  Percentage4 INT
)

INSERT INTO @FooTable
(
    Name, IdNumber,
    Subject1, Percentage1,
    Subject2, Percentage2,
    Subject3, Percentage3,
    Subject4, Percentage4
)


 VALUES
(   'Name 1', -- Name - varchar(10)
    1,
    'Subject 1', -- Subject1 - varchar(10)
    10,  -- Percentage1 - int
    'Subject 2', -- Subject2 - varchar(10)
    20,  -- Percentage2 - int
    'Subject 3', -- Subject3 - varchar(10)
    30,  -- Percentage3 - int
    'Subject 4', -- Subject4 - varchar(10)
    40   -- Percentage4 - int
    )
, ('Name 2', -- Name - varchar(10)
    2,
    'Subject 1', -- Subject1 - varchar(10)
    20,  -- Percentage1 - int
    'Subject 2', -- Subject2 - varchar(10)
    30,  -- Percentage2 - int
    'Subject 3', -- Subject3 - varchar(10)
    40,  -- Percentage3 - int
    'Subject 4', -- Subject4 - varchar(10)
    50   -- Percentage4 - int)
    )
, ('Name 3', -- Name - varchar(10)
    3,
    'Subject 1', -- Subject1 - varchar(10)
    30,  -- Percentage1 - int
    'Subject 2', -- Subject2 - varchar(10)
    40,  -- Percentage2 - int
    'Subject 3', -- Subject3 - varchar(10)
    50,  -- Percentage3 - int
    'Subject 4', -- Subject4 - varchar(10)
    60   -- Percentage4 - int)
    )

SELECT * FROM @FooTable

Unpivoting data and pivoting data:

;WITH cte
AS (SELECT *,
           ROW_NUMBER() OVER (PARTITION BY Name ORDER BY percentage DESC) AS rn
    FROM
    (
        SELECT Name,               
               Percentage1,
               Percentage2,
               Percentage3,
               Percentage4
        FROM @FooTable
    ) p
        UNPIVOT
        (
            percentage
            FOR subject IN (Percentage1, Percentage2, Percentage3, Percentage4)
        ) up)
SELECT *
FROM (
SELECT cte.percentage, cte.subject
FROM cte
WHERE rn IN ( 1, 2, 3, 4 )
)source
PIVOT
(
    MAX(percentage)
    FOR subject IN ([Percentage1],[Percentage2],[Percentage3],[Percentage4])
)q

Output:

enter image description here

Upvotes: 1

jhinghaus
jhinghaus

Reputation: 855

The data structure is build in a way that makes things much more complicated as they could be. You are not allowed to change that. Ok. We need to do something ugly to deal with that ugly data structure.

Define a view that normalizes the structure. Then you can work in a normal way querying that view.

The view would look like:

select Name, IdNumber, Subject1 as Subject, Percentage1 as Percentage, 1 as SubjectNumber
union all
select Name, IdNumber, Subject2 as Subject, Percentage2 as Percentage, 2 as SubjectNumber
...

That should be much easier to deal with.

If this was for production somehow, you should try to set things straight much earlier.

Upvotes: 1

Sanal Sunny
Sanal Sunny

Reputation: 617

If you don't wish to change your table structure then you need to UNPIVOT the data after that you can select the top 4 subjects

;WITH cte AS (
SELECT *,ROW_NUMBER() OVER (PARTITION BY Name  ORDER BY Percentage DESC) AS rn
FROM 
(SELECT Name,percentage1,percentage2,percentage3,percentage4
            ,percentage5,percentage6,percentage7,percentage8
            ,percentage9,percentage10,percentage11,percentage12
FROM r ) p
UNPIVOT 
 (percentage  FOR subject IN (,percentage1,percentage2,percentage3,percentage4
                              ,percentage5,percentage6,percentage7,percentage8
                             ,percentage9,percentage10,percentage11,percentage12)
  ) up )
 SELECT * FROM cte
 WHERE rn IN (1,2,3,4)

Upvotes: 3

Related Questions