CODEWITHSUNDEEP
sql
Samuel Mathews
Samuel Mathews

Reputation: 171

order by not working as expected

I would like to pull in all bday members between two dates - irrespective of year.. just with month and day.. i get the results properly retrieved, but not ordered as i expect...

i would need the records ordered month by month with days in ascending order - such that those members in one month is grouped with the days being in ascending order.. (i get it grouped by month but not by days in it)

query:

  SELECT * 
  FROM family_member 
  WHERE DATE_FORMAT(dob, '%m-%d') 
          BETWEEN DATE_FORMAT('2013-07-1', '%m-%d') 
             AND DATE_FORMAT('2013-09-30', '%m-%d') 
  order by MONTH(dob)
          ,DAYOFMONTH(dob) 
          and status='a'

Upvotes: 0

Views: 51

Answers (1)

D Stanley
D Stanley

Reputation: 152644

I think you need to take out the and in the order by:

SELECT * FROM family_member 
  WHERE DATE_FORMAT(dob, '%m-%d') BETWEEN DATE_FORMAT('2013-07-1', '%m-%d') AND DATE_FORMAT('2013-09-30', '%m-%d') 
  order by MONTH(dob), DAYOFMONTH(dob), status='a'

As written, it's ordering by MONTH(dob), then be the boolean result of ANDing DAYOFMONTH(dob) and status='a'

either that or you've inserted the order by in the middle of your where clause:

SELECT * FROM family_member 
  WHERE DATE_FORMAT(dob, '%m-%d') BETWEEN DATE_FORMAT('2013-07-1', '%m-%d') AND DATE_FORMAT('2013-09-30', '%m-%d') 
     and status = 'a'
  order by MONTH(dob), DAYOFMONTH(dob)

Upvotes: 3

Related Questions