We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Practice
  2. SQL
  3. Basic Join
  4. The Report
  5. Discussions

The Report

  • priyanshu281 + 1 comment

    Hi Huang, even I am also using this but having wrong order when grade<8 i.e when it is NUll not able to do order it in ascending order of marks.I tried using your query as well facing same issue. I guess we have to use union. Please suggest me with this!!

    • pureabsolute + 1 comment

      priyanshu281, if it is having trouble with NULL and order, try converting the null into a 0.

      The orderby line becomes:

      order by G.Grade DESC, ISNULL(name, 0), Marks

      • kcuck + 1 comment

        WORKED FOR ME

        SELECT Name = CASE WHEN G.Grade >7 THEN S.Name ELSE NULL END, G.Grade , S.Marks FROM Students S INNER JOIN Grades G ON S.Marks >= G.Min_Mark AND S.Marks <= G.Max_Mark ORDER BY Grade DESC,Name,Marks;

        • andremlsantos + 1 comment

          I can't use this script. In the name field, its printed 1, instead of the correct name. This is the output returned:

          1 10 95 
1 10 94 
1 10 96 
1 10 100 
1 10 99 
1 9 89 
1 9 88 
1 9 81 
1 9 87 
1 9 80 
1 9 84 
1 8 77 
1 8 78 
1 8 74 
1 8 76 
1 8 77 
NULL 7 64 
NULL 7 66 
NULL 6 55 
NULL 4 34 
NULL 3 24
          • colonel_bishop + 1 comment

            in place of 'SELECT NAME = CASE' in first line, use 'SELECT CASE' because I think the former one is evaluating in 1 and 0(boolean)

            • anusweetyda + 1 comment

              why should we use case..? pls explain..

              • plj_coding + 0 comments

                It adds a conditional data value for the name column. The name of the student in the current row or NULL