why wont my order by work when i have group by before it RSS

• • Reply
  

  cubangt

  Contributor

  3052 Points

  2402 Posts

  why wont my order by work when i have group by before it

  Apr 18, 2011 04:34 PM|LINK

  I have the following setup, but when you review the results, they arent ordered correctly. Since the date was converted to varchar, it puts the dates in numerical order.. instead of actual date order.. but if i switch the order by to this, it complains that its not contained in aggregate function bla bla bla.. how can i truely order the results by date and still have my group by conditons.

  ORDER BY EventRptDate DESC

   

  GROUP BY NodeId,CONVERT(VARCHAR(10),EventRptDate, 101),FirmwareVersion,OverallHealth
  
  ORDER BY CONVERT(VARCHAR(10),EventRptDate, 101) DESC

  Intermediate ASP.net User, Using VS2008/VS2010 with C# and SQL2005, SQL2008, Silverlight 3
  ---------------------
  Mark as Answered if it helped
• • Reply
  

  TabAlleman

  All-Star

  15571 Points

  2700 Posts

  Re: why wont my order by work when i have group by before it

  Apr 18, 2011 06:00 PM|LINK

  Everywhere you have this:   CONVERT(VARCHAR(10),EventRptDate, 101),

  Replace it with this:   DATEADD(dd, DATEDIFF(dd, 0, EventRptDate), 0)

  -Tab Alleman
• • Reply
  

  cubangt

  Contributor

  3052 Points

  2402 Posts

  Re: why wont my order by work when i have group by before it

  Apr 18, 2011 07:05 PM|LINK

  Seems to work, but i need the output in the 101 format as i had before.. right now with this change the date is returned 2010-11-12 00:00:00:000

   

  Intermediate ASP.net User, Using VS2008/VS2010 with C# and SQL2005, SQL2008, Silverlight 3
  ---------------------
  Mark as Answered if it helped
• • Reply
  

  limno

  All-Star

  117326 Points

  8003 Posts

  Moderator

  MVP

  Re: why wont my order by work when i have group by before it

  Apr 18, 2011 08:14 PM|LINK

  You can just CAST your ORDER BY part to datatime for sorting:

   

  ORDER BY CAST(CONVERT(VARCHAR(10),EventRptDate, 101) as datetime) DESC

  Limno
  
  Format your SQL query with instant sql formatter:
  http://www.dpriver.com/pp/sqlformat.htm
  • Marked as answer by cubangt on Apr 18, 2011 08:33 PM
• • Reply
  

  cubangt

  Contributor

  3052 Points

  2402 Posts

  Re: why wont my order by work when i have group by before it

  Apr 18, 2011 08:34 PM|LINK

  that seems to be it..

  thanks..

  Intermediate ASP.net User, Using VS2008/VS2010 with C# and SQL2005, SQL2008, Silverlight 3
  ---------------------
  Mark as Answered if it helped