Solved

SQL query using OVER(PARTITION...)

Posted on 2012-03-28
7
453 Views
Last Modified: 2012-03-30
I have a database with 400 tables.  Each table has the following columns with data types listed and a sample of the values in the columns.  Table T254, for example, looks like this:

[TIME], datetime(2):  2012-01-01 08:32:09.4410000
VALUE, real:  435.3914

There are several values for each day.  I want a result that includes the VALUE associated with the latest time of the last day of each month.  In other words max([time]), but for each month.  I have been trying to use OVER(partition by...), but I have not been able to get it to work.  The result set should look something like this:

YEAR          MONTH          VALUE
2012               1                 1234.9876
2012               2                 5432.2343
2012               3                 9999.9999

Where the value for each month represents the value column for the latest time on the last day of the month.  The times are irregular.  One month the last time may be 23:59:25.00000 and the next month it may be 22:34:52.00876.

How can I write a query to accomplish this?

T
0
Comment
Question by:thoecherl
  • 4
  • 3
7 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 37779984
This query will simply do:

SELECT YEAR([TIME]), MONTH([TIME]), MAX(VALUE)
FROM T254
GROUP BY YEAR([TIME]), MONTH([TIME])
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 37779989
If you want to use several other columns in the SELECT clause, then use PARTITION BY query as listed below:

SELECT *
FROM (
SELECT *, VALUE, ROW_NUMBER() OVER ( PARTITION BY YEAR([TIME]), MONTH([TIME]) ORDER BY [TIME] DESC) RNUM
FROM T254 ) temp
WHERE RNUM = 1
0
 

Author Comment

by:thoecherl
ID: 37781724
Thanks rrjegan17.  I don't think your first suggestion will work because it's not the MAX(VALUE) I am after, but the VALUE associated with the MAX(TIME) for each month.  I will have thousands of rows with a TIME and a VALUE column in each row for each month.  I want the result set to show only the value associated with the last entry for the month (MAX(TIME)).

I will try the second query as soon as I get to the client site and see what that does.  Thanks.

T
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:thoecherl
ID: 37783025
I've requested that this question be closed as follows:

Accepted answer: 0 points for thoecherl's comment #37781724

for the following reason:

Thank you rrjegan17. &nbsp;The second query seems to work. &nbsp;I missed the ROW_NUMBER trick. &nbsp;I knew the OVER clause was the answer but without ROW_NUMBER, I couldn't get it to partition the way I wanted it to. &nbsp;Thanks again.<br /><br />T
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 37783026
Since you mentioned that my second query worked, I would request you to close the question by awarding the points to me..
Hence objecting to close the question..

Thank you rrjegan17.  The second query seems to work.  I missed the ROW_NUMBER trick.  I knew the OVER clause was the answer but without ROW_NUMBER, I couldn't get it to partition the way I wanted it to.  Thanks again.
0
 

Author Closing Comment

by:thoecherl
ID: 37783494
This should work and give you the points, rrjegan17.  Thanks for your help.  I didn't mean to stiff you on the points.  An honest mistake -- still getting used to the new format of the site.

T
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 37786600
thoecherl,

No issues. Even I'm slightly confused with the new site and my activity is reduced out of that. :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

867 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

16 Experts available now in Live!

Get 1:1 Help Now