Solved

Last 12 records

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now