Solved

Last 12 records

Posted on 2008-10-17
4
283 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

717 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