Solved

Last 12 records

Posted on 2008-10-17
4
278 Views
Last Modified: 2010-04-21
I need to pick out the last 12 records from a dataset. My SQL code uses some casting so I think it has problems with Desc along with TOP 12. Here is my SQL

SELECT TOP 12 CAST(YEAR([Rdate]) AS VarChar(20)) + '-' + CAST(MONTH([Rdate]) AS VarChar(20)) AS Month, Count(*)  from
table1
GROUP BY CAST(YEAR([Rdate]) AS VarChar(20)) + '-' + CAST(MONTH([Rdate]) AS VarChar(20))
ORDER BY CAST(YEAR([Rdate]) AS VarChar(20)) + '-' + CAST(MONTH([Rdate]) AS VarChar(20)) Desc
0
Comment
Question by:Mr_Shaw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 22739159
Hello Mr_Shaw,

Probably because the months 1-9 are cast as single digits and will interfere with your expected ordering. It might be better to use:

Select Top 12 Convert(varchar(7),Rdate,120),Count(*)
From table1
Group By Convert(varchar(7),Rdate,120)
Order By Convert(varchar(7),Rdate,120) Desc

Regards,

TimCottee
0
 

Author Comment

by:Mr_Shaw
ID: 22739182
Thanks, code worked.

I need the dates to read month-year not year-month. Is there any way I can do this?
0
 
LVL 43

Accepted Solution

by:
TimCottee earned 500 total points
ID: 22739452
Mr_Shaw,

You can select a different value than the order by expression so:

SELECT TOP 12 CAST(Month([Rdate]) AS VarChar(20)) + '-' + CAST(Year([Rdate]) AS VarChar(20)) AS Month, Count(*)  from
table1
GROUP BY CAST(Month([Rdate]) AS VarChar(20)) + '-' + CAST(Year([Rdate]) AS VarChar(20))
Order By Convert(varchar(7),Rdate,120) Desc

Would display in MM-yyyy format but the ordering would be in yyyy-mm order.
TimCottee
0
 

Author Closing Comment

by:Mr_Shaw
ID: 31507085
thanks
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question