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;
精彩评论