Order by doesn't work in MS SQL

I have the following SQL statement that returns the correct rows, but they are out of order. They are the days of the week, but I want them in the correct order, such as Sunday, Monday, Teuesday, etc.  The order by on this statement does not return any rows. If I take the order by off, it returns the correct rows, just not in the order I would like. Here it is:

Dim strDayQuery As String = "Select id, InstrumentID, TeacherID, DATENAME(dw , EventStart) As Day, DATEPART(dw, EventStart) As Day2 FROM Events WHERE id IN (SELECT MAX(id) FROM Events WHERE InstrumentID = @InstrumentID And TeacherID = @TeacherID And Available <> 'False' Group by DATENAME(dw , EventStart) Order by DATEPART(dw, EventStart) )"
LVL 1
mlg101Asked:
Who is Participating?
 
apresenceCommented:
Your ORDER BY clause is in the sub-query, you want it in the parent query.  See attached SQL statement (I cleaned it up a little bit):
Your ORDER BY query is buried in the subquery.  Try moving it to the outer query:
SELECT
  id,
  InstrumentID,
  TeacherID,
  DATENAME(dw , EventStart) AS Day,
  DATEPART(dw, EventStart) AS Day2
FROM
  Events
WHERE
  id IN (
    SELECT
      MAX(id)
    FROM
      Events
    WHERE
      InstrumentID = @InstrumentID And
      TeacherID = @TeacherID And
      Available <> 'False'
    GROUP BY
      DATENAME(dw, EventStart)
  )
ORDER BY
  DATEPART(dw, EventStart)

Open in new window

0
 
Mohit VijayCommented:
try this

Dim strDayQuery As String = "Select id, InstrumentID, TeacherID, DATENAME(dw , EventStart) As Day, DATEPART(dw, EventStart) As Day2 FROM Events WHERE id IN (SELECT MAX(id) FROM Events WHERE InstrumentID = @InstrumentID And TeacherID = @TeacherID And Available <> 'False' Group by DATENAME(dw , EventStart)) Order by DATEPART(dw, EventStart)"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.