locked
Using Order by in a Group by statement RRS feed

  • Question

  • is it possible to use a order by clause in a group by statement?

    this is my statement

     

    SELECT 
    	  [Month]
    	, Count(*)
    FROM
    	Students
    WHERE
    	TotalAttendances = '1'
    	AND ([Class] = '5d' 
    		Or [Class] = '9s' 
    		Or [Class] = '8q' 
    		Or [Class] = '1b')
    GROUP BY
    	  [Month]
    ORDER BY 
    	AttendanceDate

    Wednesday, August 5, 2009 11:52 AM

Answers

  • this ?
    SELECT
          [Month]
        , Count(*)
    FROM
        Students
    WHERE
        TotalAttendances = '1'
        AND ([Class] = '5d'
            Or [Class] = '9s'
            Or [Class] = '8q'
            Or [Class] = '1b')
    GROUP BY
          [Month]
    ORDER BY
        MAX(AttendanceDate)



    KH Tan
    • Marked as answer by SQL_1980 Wednesday, August 5, 2009 12:27 PM
    Wednesday, August 5, 2009 12:17 PM

All replies

  • if you want to use column in order by clause that is not in your select statement then you have to add it in your select.

    by the way why you want to use attendaceDate in order when you do not want to show it or use it in count.
    Shamas Saeed MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com
    • Proposed as answer by Azher Aziz Wednesday, August 5, 2009 12:29 PM
    Wednesday, August 5, 2009 12:11 PM
  • this ?
    SELECT
          [Month]
        , Count(*)
    FROM
        Students
    WHERE
        TotalAttendances = '1'
        AND ([Class] = '5d'
            Or [Class] = '9s'
            Or [Class] = '8q'
            Or [Class] = '1b')
    GROUP BY
          [Month]
    ORDER BY
        MAX(AttendanceDate)



    KH Tan
    • Marked as answer by SQL_1980 Wednesday, August 5, 2009 12:27 PM
    Wednesday, August 5, 2009 12:17 PM
  • because the month is out of order

    Month (No column name)
    Apr 52
    Jul 12
    Jun 67
    May 24
    Wednesday, August 5, 2009 12:18 PM
  • many thanks
    Wednesday, August 5, 2009 12:28 PM
  • This is a design flaw. Class and Attendance are properties of a Schedule.
    Wednesday, August 5, 2009 12:38 PM