• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 468
  • Last Modified:

SQL query using OVER(PARTITION...)

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
T Hoecherl
Asked:
T Hoecherl
  • 4
  • 3
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
This query will simply do:

SELECT YEAR([TIME]), MONTH([TIME]), MAX(VALUE)
FROM T254
GROUP BY YEAR([TIME]), MONTH([TIME])
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
T HoecherlDeveloperAuthor Commented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
T HoecherlDeveloperAuthor Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
T HoecherlDeveloperAuthor Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
thoecherl,

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

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now