Tutorial :select maximum two mark out of three



Question:

I have table contain the mark for three exam for one subject each mark in different column how to select best two mark from the three column ?


Solution:1

It will be horrendous - try:

select case when mark1 >= mark2 and mark1 >= mark3 then mark1              when mark2 >= mark1 and mark2 >= mark3 then mark2              else mark3 end as first,         case when mark1 >= mark2 and mark1 <= mark3 then mark1              when mark1 >= mark3 and mark1 >= mark2 then mark1              when mark2 >= mark1 and mark1 >= mark3 then mark2              when mark2 >= mark3 and mark1 >= mark1 then mark2              else mark3 end as second  from my_table;  

That will teach you to store each mark in a separate row in future ;-)


Solution:2

first, you should really normalize the table. second, there's an easy way to normalize for the purpose of a single query: CTEs.

BEGIN;    CREATE SCHEMA sogrades;  SET search_path TO sogrades;    CREATE DOMAIN grade AS  INT  CHECK (VALUE BETWEEN 1 AND 5)  ;    CREATE TABLE grades (    student INT NOT NULL PRIMARY KEY  , grade1 GRADE  , grade2 GRADE  , grade3 GRADE  );    INSERT INTO grades (student, grade1, grade2, grade3)  VALUES    (10, 1, 3, 5)  , (20, 4, 3, 2)  , (30, 1, 2, 1)  ;    WITH g (student, grade) AS (    SELECT student, grade1 FROM grades UNION    SELECT student, grade2 FROM grades UNION    SELECT student, grade3 FROM grades  )  SELECT DISTINCT gl.student, gl.grade  FROM g gl, g gr   WHERE gl.student = gr.student    AND gl.grade < gr.grade  ORDER BY student, grade;    ROLLBACK;  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »