MS-SQL sort output in descending order

Question

I have this MS-SQL query with thousands of row records in database:

SELECT DISTINCT TOP 7 DATENAME(MM, mydatetime) + ' ' + CAST(DAY(mydatetime) AS VARCHAR(2)) as thedate
    , MONTH(mydatetime)
    , DAY(mydatetime)
    , COUNT(Page) as totalcount
    , count(DISTINCT Page) as visitors
  FROM someTable
  WHERE Page LIKE '%AEC%'
  GROUP BY DATENAME(MM, mydatetime) + ' ' + CAST(DAY(mydatetime) AS VARCHAR(2))
    , MONTH(mydatetime)
    , DAY(mydatetime)
  ORDER BY MONTH(mydatetime) DESC
    , DAY(mydatetime) DESC

It will output this:

thedate     | totalcount | visitors
-----------------------------------
October 17  |     4      |    1
October 15  |     1      |    1
October 12  |     1      |    1
October 3   |     3      |    3
October 2   |     42     |    22
September 28|     2      |    1
September 21|     14     |    10

My problem is that I simply cant output this in descending order so it will look like this:

thedate     | totalcount | visitors
-----------------------------------
September 21|     14     |    10
September 28|     2      |    1
October 2   |     42     |    22
October 3   |     3      |    3
October 12  |     1      |    1
October 15  |     1      |    1
October 17  |     4      |    1

So any help is greatly appreciated.

Your example output appears to me to be in ASCENDING order not DESCENDING order. ASCENDING goes from earlier to later. DESCENDING goes from later to earlier. — RThomas
– RThomas, Commented Oct 18, 2012 at 18:40
What makes you think that your desired output is on descending order?, do you mean ascending order?, in that case, just take out the DESC — Lamak
– Lamak, Commented Oct 18, 2012 at 18:40
If I do that, it will give me first 7 records from database dating like back from last year — crashtestxxx
– crashtestxxx, Commented Oct 18, 2012 at 18:41
@crashtestxxx - So your actual requirement is different that the one you posted. You need the last 7 records, returned in ascending order — Lamak
– Lamak, Commented Oct 18, 2012 at 18:43
@Lamak please tell me how it accept order by inside subquey it always gives me error Msg 1033, Level 15, State 1, Line 16 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. — nesreen
– nesreen, Commented Oct 2, 2014 at 13:28

Lamak · Accepted Answer · 2012-10-18 18:49:01Z

9

For your actual requirement, you can use your current query as a derived table and order that result in the way you want:

SELECT *
FROM (  SELECT DISTINCT TOP 7 DATENAME(mm, mydatetime) + ' ' 
                              + CAST(DAY(mydatetime) AS VARCHAR(2)) AS thedate, 
                              MONTH(mydatetime)                     AS theMonth, 
                              DAY(mydatetime)                       AS theDay, 
                              COUNT(page)                           AS totalcount, 
                              COUNT(DISTINCT page)                  AS visitors 
        FROM   sometable 
        WHERE  page LIKE '%AEC%' 
        GROUP  BY DATENAME(mm, mydatetime) + ' ' 
                  + CAST(DAY(mydatetime) AS VARCHAR(2)), 
                  MONTH(mydatetime), 
                  DAY(mydatetime) 
        ORDER  BY MONTH(mydatetime) DESC, 
                  DAY(mydatetime) DESC) A
ORDER BY theMonth, theDay

answered Oct 18, 2012 at 18:49

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lamak Over a year ago

@RThomas - well, I'm hoping that this is what the op actually wants :-)

crashtestxxx Over a year ago

Thank you so much, this is exactly what I needed. Sorry if I was not clear with my question.

nesreen Over a year ago

@Lamak please tell me how it accept order by inside subquey it always gives me error Msg 1033, Level 15, State 1, Line 16 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified

Vinod Vishwanath · Accepted Answer · 2012-10-18 18:41:38Z

0

You need to remove the DESC from your ORDER BY clause. Your result is in exact reverse order because of DESC!

answered Oct 18, 2012 at 18:41

Vinod Vishwanath

5,9212 gold badges29 silver badges41 bronze badges

3 Comments

crashtestxxx Over a year ago

I will get last year 7 records from database. i need 7 most current records.

Vinod Vishwanath Over a year ago

The ORDER BY clause does not alter the set of records you will get. It only changes the order of the resultant records. If you aren't getting the right records, it means something is wrong before or including the WHERE clause. I notice your query has no mention of 'YEAR'? Could that be a problem?

Vinod Vishwanath Over a year ago

On second thoughts, you can do a nested select to get the desired result. Select * from (select.....//your current query) TableA ORDER BY month, day;

RThomas · Accepted Answer · 2012-10-18 18:43:30Z

0

To get the order you specify including only the dates you want in the return results you need to remove the desc and add a date filter to your where clause.

That may also allow you to get rid of the top statement if your do your filter right.

SELECT DISTINCT TOP 7 DATENAME(MM, mydatetime) 
    + ' ' + CAST(DAY(mydatetime) AS VARCHAR(2)) as thedate
    , MONTH(mydatetime)
    , DAY(mydatetime)
    , COUNT(Page) as totalcount
    , count(DISTINCT Page) as visitors
  FROM someTable
  WHERE Page LIKE '%AEC%' 
    AND /* filter date range i.e. MONTH(mydatetime) > 9 AND YEAR(mydatetime) > 2011 */
  GROUP BY DATENAME(MM, mydatetime) + ' ' + CAST(DAY(mydatetime) AS VARCHAR(2))
    , MONTH(mydatetime)
    , DAY(mydatetime)
  ORDER BY MONTH(mydatetime) 
    , DAY(mydatetime)

answered Oct 18, 2012 at 18:43

RThomas

10.9k2 gold badges50 silver badges65 bronze badges

6 Comments

crashtestxxx Over a year ago

Iv tried to use WHERE mydatetime> GETDATE()-8 AND Page LIKE '%AEC%', but it gives me some nonsense data back.

RThomas Over a year ago

GETDATE()-8 only goes back 8 days. Most of the records in your example occur earlier than 8 days ago.

crashtestxxx Over a year ago

DATE_ADD Very useful as well. Thank you.

crashtestxxx Over a year ago

Oh, just realized Its for MySQL. I get error "it is not recognized built in function" in MS-SQL.

RThomas Over a year ago

In that case use DateAdd msdn.microsoft.com/en-us/library/ms186819(v=sql.105).aspx Not sure why I thought you were using MySQL - I see your question title now.

|

Collectives™ on Stack Overflow

MS-SQL sort output in descending order

3 Answers 3

3 Comments

3 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related