order by group by problem

**todd2006** · todd2006, March 7th, 2009 06:47 PM

Here is my code I am having problem with the order by clause

Code:

sqlstr="SELECT Distinct MONTH(Reg_Date) AS Expr1, YEAR(Reg_Date) AS Expr2 FROM Registration  GROUP BY Reg_Date"

When it displays the results they are like this

1/2008
3/2008
2/2009
4/2008
3/2009

the records are not ordered properly I need them to be ordered in desc manner

can someone tell me how I can do it

todd

**micky** · micky, March 8th, 2009 03:01 AM

i cant see any "order by" clause in ur sql

**todd2006** · todd2006, March 8th, 2009 11:32 AM

Micky,

I tried the order by clause before group by

sqlstr="SELECT Distinct MONTH(Reg_Date) AS Expr1, YEAR(Reg_Date) AS Expr2 FROM Registration order by Reg_Date desc GROUP BY Reg_Date"

its not working

**mehere** · mehere, March 8th, 2009 01:53 PM

The ORDER BY clause should be after your GROUP BY clause.

**don94403** · don94403, March 8th, 2009 04:47 PM

Micky and Mehere are correct. ORDER BY and GROUP BY do entirely different things. You need to understand what each does and when you use one, the other, or both.

**todd2006** · todd2006, March 9th, 2009 01:25 PM

Ok this is what I am doing.

I want to display the distinct month/year in a drop down and those should be ordered in desc

then when the admin clicks on the month/year he will see all the students registered for that specific month and year.

right now with my query it does the distinct month/year but the order by clause is not working.

I tried to put the order by clause after group by and it still didnt work.

any idea

**mehere** · mehere, March 9th, 2009 01:34 PM

try this:

Code:

sqlstr="SELECT Distinct MONTH(Reg_Date) AS month_date, YEAR(Reg_Date) AS year_date FROM Registration ORDER BY CInt(YEAR(Reg_Date)) desc, CInt(MONTH(Reg_Date)) desc"

and you need to tell us more than "it didn't work". this doesn't help us know what the issue is.

**todd2006** · todd2006, March 9th, 2009 01:55 PM

sorry abt that mehere it gives an error

ORDER BY clause (CInt(YEAR(Reg_Date))) conflicts with DISTINCT.

**Wolffy** · Wolffy, March 9th, 2009 03:04 PM

Try the following query. GROUP BY and DISTINCT oft times do the same thing.

Code:

SELECT MONTH(Reg_Date) AS Expr1
     , YEAR(Reg_Date) AS Expr2 
  FROM Registration 
GROUP BY YEAR(Reg_Date), MONTH(Reg_Date)
ORDER BY YEAR(Reg_Date) desc , MONTH(Reg_Date) desc

I admit to not trying this with sample data, but it does parse.

**todd2006** · todd2006, March 9th, 2009 03:16 PM

yes it worked out thank you so much.

Thread: order by group by problem

LinkBack

Thread Tools

Display

order by group by problem

Similar Threads

Order by

Sql group by

order by row1 + row2

Summing for each group only

How can I randomly order the contents of an array?

Bookmarks

Bookmarks

Posting Permissions