Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2013
    Posts
    41

    Unanswered: Confusion in understanding the Question for SQL Query. Please Clarify !?

    Here is the Question : "List the course ID, course name, section, student ID, and student name for CRN 1003. Display the list in ascending order of student last and first names."

    My QUESTION:

    Since I need to display the list in ascending order of student last name and first name. I'm wondering how can I achieve both in one ORDER By clause? I have written the following Query:

    Code:
    SELECT A.cid, A.cname,
    B.sid, B.lname,B.fname,
    C.section,c.crn
    FROM courses A, students B, sections C, Registration D
    WHERE A.cid = C.cid AND
    B.sid = D.sid AND
    c.crn = d.crn AND
    c.crn = 1003 ;
    ORDER BY B.lname ASC;
    In the above query, I can't include B.Fname along with B.lname. Please correct me if I'm wrong somewhere. I'm confused with the last line of the question "Display the list in ascending order of student last and first names"

    Thanks

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by Jack_Tauson_Sr View Post
    In the above query, I can't include B.Fname along with B.lname.
    Why not? Are you getting an error of some sort?

    The order by clause allows multiple column names. Ascending is the default. So just specifying the columns will give you your normal alpha order sort. Now if the requirements were for a reverse sort then you could use DESC after each column name.

    The code below is untested, but should work giving the info in last name ASC order with the first names sorted ASC within the last name.

    Code:
     SELECT B.LName, B.FName, A.cid, A.cname, B.sid, C.section, C.crn
      FROM sections C
      INNER JOIN courses A      on A.cid = C.cid
      INNER JOIN registration D on D.crn = C.crn
      INNER JOIN students B     on B.sid = D.sid
     WHERE C.crn = 1003
     ORDER BY B.LName ASC, B.FName

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Or, shortly,
    Code:
    order by b.lname, b.fname

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •