select maximum two mark out of three

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 ?

---

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 ;-)

---

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;