The Report

Sort by

recency

|

3382 Discussions

|

  • + 0 comments
    SELECT  CASE
               WHEN G.Grade < 8 THEN NULL
               ELSE S.Name
            END AS Name,
            G.Grade ,
            S.Marks
    FROM Students S 
    JOIN Grades G ON S.Marks BETWEEN G.Min_Mark AND G.Max_Mark
    ORDER BY 
        G.Grade DESC,
        CASE WHEN G.Grade >= 8 THEN S.Name END ASC,
        S.Marks ASC;
    
  • + 0 comments

    **MS SQL ** key takeaways:- case in order by; and case in left joins; and union cannot be done in two different selects with - diff order by clauses for two selects

    -- Selecting name, grade, and marks from students and grades tables
    select 
        case 
            -- If grade is 8 or higher, show the actual name
            when b.grade >= 8 then a.name 
            -- Otherwise, display 'null' instead of the name
            else 'null'  
        end as name,
        b.grade,  -- Grade assigned based on marks
        a.marks   -- Original marks of the student
    from students a
    left join grades b 
    on b.grade = case 
                    -- Mapping marks to grades based on given conditions
                    when a.marks >= 90 and a.marks <= 100 then 10
                    when a.marks >= 80 and a.marks < 90 then 9
                    when a.marks >= 70 and a.marks < 80 then 8
                    when a.marks >= 60 and a.marks < 70 then 7
                    when a.marks >= 50 and a.marks < 60 then 6
                    when a.marks >= 40 and a.marks < 50 then 5
                    when a.marks >= 30 and a.marks < 40 then 4
                    when a.marks >= 20 and a.marks < 30 then 3
                    when a.marks >= 10 and a.marks < 20 then 2
                    when a.marks >= 0 and a.marks < 10 then 1
                 end
                 
    -- Sorting the results according to the given requirements
    order by 
        b.grade desc,  -- Higher grades come first
        case 
            -- If grade is 8 or higher, sort names alphabetically
            when b.grade >= 8 then a.name 
            -- Otherwise, keep them in the same order
            else null 
        end asc, 
        a.marks asc;  -- For grades below 8, sort by marks in ascending order
    

    `

  • + 0 comments

    select if (g.grade >= 8, s.name, NULL) as names, g.grade, s.marks from students s inner join grades as g on s.marks <= g.max_mark and s.marks >= g.min_mark order by g.grade desc, name asc, marks asc;

  • + 0 comments

    SELECT CASE WHEN g.grade >= 8 THEN s.name ELSE 'NULL' END AS name, g.grade, s.marks FROM students s JOIN grades g ON s.marks BETWEEN g.min_mark AND g.max_mark ORDER BY g.grade DESC,
    CASE WHEN g.grade >= 8 THEN s.name END ASC, CASE WHEN g.grade < 8 THEN s.marks END ASC;

  • + 0 comments

    SELECT CASE WHEN g.grade < 8 THEN null ELSE s.name END , g.grade, s.marks FROM students s JOIN grades g ON s.marks BETWEEN g.min_mark AND g.max_mark ORDER BY g.grade DESC, s.name ASC;