Solved

Last 12 records

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

911 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

23 Experts available now in Live!

Get 1:1 Help Now